How to use result of an SQL select statement in dbo.fnSplit function as input

Question

I have a requirement to use a comma seperated string (which is a value in a table column) in 'IN' clause of an SQL statement(SQL server 2008) For this I am using below split function to make the string in a tabular format and use it in the 'IN' clause of an SQL query.

ALTER FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
 , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
 ) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
 DECLARE @sItem VARCHAR(8000)
 WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
  BEGIN
    SELECT
     @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX
      (@sDelimiter,@sInputList,0)-1))),  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX
      (@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

  IF LEN(@sItem) > 0
    INSERT INTO @List SELECT @sItem
  END

 IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
     RETURN
  END
GO

select * from dbo.fnSplit('aaa,bbb,ccc', ',')

Above select statement gives result as:

Item
aaa
bbb
ccc

Now I need to use my SQL statement which returns the string aaa,bbb,ccc in the fnSplit function as below

  select * from dbo.fnSplit((SELECT Prefix2Include FROM dbo.vw_PrefixToInclude), ',')

Note: SELECT Prefix2Include FROM dbo.vw_PrefixToInclude returns aaa,bbb,ccc

But this gives me some syntax error as below:

Msg 102, Level 15, State 1, Line 4
  Incorrect syntax near '('.
  Msg 102, Level 15, State 1, Line 4
  Incorrect syntax near ',

Please guide me on this.

Thanks, Soumya

@MitchWheat he said thanks for the answer but he didn't accepted it. haha — Albert Laure, Nov 07 '13 at 02:36

codingbiz · Answer 1 · 2012-11-05T05:42:05.213

1

Why not try it this way

declare @Prefix2Include as Varchar(500)

SELECT  @Prefix2Include = Prefix2Include FROM dbo.vw_PrefixToInclude

select * from dbo.fnSplit(@Prefix2Include , ',')

Update

Select * From @t v 
Where v.EmpPrefix IN 
(select * from dbo.fnSplit(@Prefix2Include, ','))

edited Nov 05 '12 at 05:42

answered Nov 05 '12 at 05:17

codingbiz

26,179
8
59
96

Thanks for the answer.Actually I have a big query and I need to include this inside an 'IN' clause of one select statement of this query.My requirement is to use this tabular items as below: select * from Tbl_Emp v where v.EmpPrefix in (select * from dbo.fnSplit((SELECT Prefix2Include FROM dbo.vw_PrefixToInclude), ',')) – Soumya Nov 05 '12 at 05:20
Thanks for the answer I will try to use the same with my query inside a stored procedure – Soumya Nov 05 '12 at 05:58

score 1 · Answer 2 · answered Dec 16 '21 at 21:07

CROSS JOIN Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.

SELECT item FROM dbo.vw_PrefixToInclude CROSS APPLY dbo.fnSplit(Prefix2Include, ',')

for sql 2016 o later

SELECT value FROM dbo.vw_PrefixToInclude CROSS APPLY STRING_SPLIT(Prefix2Include, ',')

To change the compatibility level of a database, refer to View or Change the Compatibility Level of a Database. https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-ver15

How to use result of an SQL select statement in dbo.fnSplit function as input

2 Answers2

Update

Linked