CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005user-defined-functions
user1178514
user1178514

Reputation: 33

Using Variable with function - SQL

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 '.'.

Upvotes: 1

Views: 1258

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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

Upvotes: 2

StevieG
StevieG

Reputation: 8709

have you tried:

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

Upvotes: 0

Related Questions