Using Variable with function - SQL

Question

I have custom function which return a table it accepts two varchars, it splits the varchar based on the delimeter

SELECT VALUE FROM dbo.Split('xxx','_') --- working

select abc from abcd a,cde b where a.abc like (SELECT VALUE FROM dbo.Split(b.abc,'_'))-- not working


select abc from abcd a,cde b where a.abc like (SELECT VALUE FROM dbo.Split('xx','_'))-- working

select abc from abcd a,cde b where a.abc like (SELECT b.abc)-- working

How to get the not working case to work.

Error i get it Incorrect syntax near '.'.

@MikaelEriksson he wants to know why that case isn't working, check the comments. — aF.
– aF., Commented Feb 7, 2012 at 11:20
It would help if you updated your question with some sample data that causes the error and please add the error message as well. I guess you are getting something like "Subquery returned more than 1 value.". Try to change like to in instead. It might do what you want. — Mikael Eriksson
– Mikael Eriksson, Commented Feb 7, 2012 at 11:31

Lieven Keersmaekers · Accepted Answer · 2012-02-07 12:27:06Z

2

Using CROSS APPLY allows you to use a variable as a parameter to a function.

SELECT abc
FROM   abcd a
       , cde b
       CROSS APPLY (select VALUE from dbo.Split(b.abc, '_')) f
WHERE  a.abc LIKE f.Value

or

SELECT  *
FROM    abcd a
        , cde b        
        CROSS APPLY dbo.Split(b.abc, '_') f
WHERE   a.abc LIKE f.Value

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

Test script

CREATE FUNCTION dbo.Split(@a VARCHAR(4), @b VARCHAR(4)) 
RETURNS TABLE 
AS RETURN
(
  SELECT Value = 'Test'  
)
GO

;WITH abcd (abc) AS (
  SELECT 'Test'
)
, cde (abc) AS (
  SELECT 'Test'
)  
SELECT  *
FROM    abcd a
        , cde b        
        CROSS APPLY (SELECT Value FROM dbo.Split(b.abc, '_')) f
WHERE   a.abc LIKE f.Value

edited Feb 7, 2012 at 12:27

answered Feb 7, 2012 at 11:28

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1178514 Over a year ago

SELECT * FROM tablesxx c,tableyyy y CROSS APPLY (SELECT TOP 1 VALUE FROM dbo.Split(x.abc,'_')) f WHERE x.abc LIKE f.value Incorrect syntax near 'APPLY'. Incorrect syntax near '.'.

Lieven Keersmaekers Over a year ago

@user1178514 - I've missed one ). The answer has been corrected.

user1178514 Over a year ago

Thanks alot. It works now..CROSS APPLY does not work because I am on SQL SERVER 2000...so I used another method which appraently you suggested stackoverflow.com/questions/4644740/…

Lieven Keersmaekers Over a year ago

@user1178514 - It wasn't clear from your question that there was only one result returned but if it worked for you, you can mark this as answered and upvote my other answer :). See How does accepting an answer works

user1178514 Over a year ago

Marked Answered...Can't upvote have got that much reputation yet...joined recently

StevieG · Accepted Answer · 2012-02-07 11:21:36Z

0

have you tried:

select abc 
from abcd a, cde b 
where a.abc like dbo.Split(b.abc,'_')

answered Feb 7, 2012 at 11:21

StevieG

8,7391 gold badge26 silver badges33 bronze badges

1 Comment

user1178514 Over a year ago

As the function returns a table when I call it like this it throws invalid object name error.

Collectives™ on Stack Overflow

Using Variable with function - SQL

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related