5

Possible Duplicates:
Parameterizing a SQL IN clause?
SQL Server - In clause with a declared variable

Hi,

I am facing problem passing parameters to 'IN' clause. I am using the below query.

Query:

SELECT Topics.Topic_Id  FROM Topics 
    Where Topic_Description IN (''+ @Topics +'')

This query works when the parameter has single value. My parameter can have comma separated multiple values like : 'one','two','three','four'. But the query fails whenever there are multiple parameters. How to get over this ? Please suggest.

Thanks

I have to use the above as part of a stored procedure. I have to take the result of the select query into a cursor like below:

DECLARE cur_TopicIDs CURSOR FOR SELECT Topics.Topic_Id FROM Topics Where Topic_Description IN (''+ @Topics +'')....etc

In this case how can I use dynamic sp as suggested in other links

Community
  • 1
  • 1
San
  • 1,797
  • 7
  • 32
  • 56

2 Answers2

3

Use any of the split functions from here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Georgi
  • 395
  • 3
  • 4
  • 3
    Above URL which led me to the url http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx solved my problem – San Jan 18 '11 at 12:41
0

You have to create a string of format one','two','three as value in the @Topics.

EDIT:- Then you need to use EXEC sp_executesql to execute your sql statement. The answer is properly explained in the link given by spender for your question.

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
  • You're sure about this? OP doesn't make it clear where the query is being assembled. – spender Jan 18 '11 at 10:44
  • @spender - I have not tried this myself. But since its a string substitution, I suppose this will work. becacuse OP is already adding the first ' and last '. In between we need to fill it with a string of given format. – Sachin Shanbhag Jan 18 '11 at 10:46
  • This approach would be vulnerable to SQL injection. The split function is safer. – Martin Smith Jan 18 '11 at 10:48
  • @Martin - Aah.. yes. I think you are right. I did not think about SQL Injection. – Sachin Shanbhag Jan 18 '11 at 10:49
  • I am creating a string of format one','two','three as value but it is still not working – San Jan 18 '11 at 10:50
  • @San - Check the link given by spender as possible duplicate. That is very much the case you need. I think when you create a string of said format too, you need to execute this quert dynamically using exec call. – Sachin Shanbhag Jan 18 '11 at 10:52
  • But this select query is part of an SP. which goes like this - DECLARE cur_TopicIDs CURSOR FOR SELECT Topics.Topic_Id FROM Topics Where Topic_Description IN (''+ @Topics +'')....etc – San Jan 18 '11 at 11:29