Dynamic SQL Table Variable 1

[Elegabalus]

I've got a query that is dynamic sql. One of the variables (PersonID) that gets passed into the query is a comma delimited string that provides a list of PersonIDs (i.e., the string would be: "1,2,3,4,5,6").

I want to build the WHERE clause dynamically, with the sql similar to: WHERE PersonID in (1,2,3,4,5,6). I know this is not possible directly, so I'm trying to use the Split UDf mentioned here:

http://www.tek-tips.com/faqs.cfm?fid=5207.

Some code:

SELECT @sql = 'Select * FROM Table1'

SELECT @sql = @sql + ' WHERE PersonID IN (SELECT Value FROM dbo.Split(@List,','))'

However, every time I try to do this, I get the following error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.[/color red]

Apparently you can't reference a UDF in a dynamic query? I've tried doing this with table variables instead, but I get the same problem...can't assign values in the sql.

Is there a way to use a UDF in a dynamicly built sql query?

Any helps is appreciated.

 

[rsshetty]

Try this:

SELECT @sql = 'Select * FROM Table1 WHERE PersonID IN (' + @List+')'

exec(@sql)

If your string is already in the format - 1,2,3,4,5,6 you don't need to split it since the same format works for the IN operator.


rsshetty.
It's always in the details.

 

[Elegabalus]

Ah, great, thanks...I was thinking too much with regular SQL, and passing in a comma delimited list doesn't work with that.

Monday mornings.