Sending parameters into stored procedure in SQL Server 2008

Question

I have this stored procedure

Usp_temp
@temp nvarchar(50)

Where  city in (@temp)

and I try to send a num of parameters like this

Usp_temp '1,3,5'

what is the right way to do this?

Answer 1

Try to have a look at this article: http://www.codeproject.com/KB/database/SPParameters.aspx

You should be able to use this example from the article to solve your problem:

DECLARE @IDs varchar(100)
SELECT  @IDs = '429,446,552,1001, 332 , 471' 
  --Any IDs as an example

SELECT  Convert(Int, NullIf(SubString(',' + @IDs + ',' , ID , CharIndex(',' , ',' + @IDs + ',' , ID) - ID) , '')) AS IDList 
FROM    tblToolsStringParserCounter
WHERE   ID <= Len(',' + @IDs + ',') AND SubString(',' + @IDs + ',' , ID - 1, 1) = ',' 
AND     CharIndex(',' , ',' + @IDs + ',' , ID) - ID > 0 

Answer 2

Have a look at these articles: Arrays and Lists in SQL Server 2005 and Beyond, Arrays and Lists in SQL Server 2008.

They discuss probably all sane ways to pass a list of values to an SP.