将参数发送到SQL Server 2008中的存储过程

时间:2021-03-24 11:24:37

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