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., Feb 07 '12 at 11:20
Mikael need to know how can i get the not working one to work... — user1178514, Feb 07 '12 at 11:22
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, Feb 07 '12 at 11:31

Lieven Keersmaekers · Accepted Answer · 2012-02-07T12:27:06.353

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 07 '12 at 12:27

answered Feb 07 '12 at 11:28

Lieven Keersmaekers

57,207
13
112
146

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 '.'. – user1178514 Feb 07 '12 at 11:45
@user1178514 - I've missed one `)`. The answer has been corrected. – Lieven Keersmaekers Feb 07 '12 at 12:26
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 http://stackoverflow.com/questions/4644740/cross-apply-does-not-work-with-sql-server-2000 – user1178514 Feb 07 '12 at 13:33
@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](http://meta.stackexchange.com/questions/5234/) – Lieven Keersmaekers Feb 07 '12 at 14:10
1

Marked Answered...Can't upvote have got that much reputation yet...joined recently – user1178514 Feb 08 '12 at 11:39

score 0 · Answer 2 · answered Feb 07 '12 at 11:21

0

have you tried:

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

answered Feb 07 '12 at 11:21

StevieG

8,639
23
31

As the function returns a table when I call it like this it throws invalid object name error. – user1178514 Feb 07 '12 at 11:24

Using Variable with function - SQL

2 Answers2