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?
这样做的正确方法是什么?
2 个解决方案
#1
2
Have a look at these articles: Arrays and Lists in SQL Server 2005 and Beyond, Arrays and Lists in SQL Server 2008.
看看这些文章:SQL Server 2005中的数组和列表以及SQL Server 2008中的Beyond,Arrays和Lists。
They discuss probably all sane ways to pass a list of values to an SP.
他们讨论了将值列表传递给SP的所有合理方法。
#2
1
Try to have a look at this article: http://www.codeproject.com/KB/database/SPParameters.aspx
试着看看这篇文章: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
#1
2
Have a look at these articles: Arrays and Lists in SQL Server 2005 and Beyond, Arrays and Lists in SQL Server 2008.
看看这些文章:SQL Server 2005中的数组和列表以及SQL Server 2008中的Beyond,Arrays和Lists。
They discuss probably all sane ways to pass a list of values to an SP.
他们讨论了将值列表传递给SP的所有合理方法。
#2
1
Try to have a look at this article: http://www.codeproject.com/KB/database/SPParameters.aspx
试着看看这篇文章: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