当值的数量未知时，如何通过SQL参数传递值列表? [英] How to pass list of values through SQL parameter when number of values is not known?

问题描述

朋友们，


请提出一些想法，以通过SQL参数将动态创建的值传递给存储过程?

我想将这些值保存到表中，在该表中也动态创建该表.




谢谢

Siva

Hi friends,


Please suggest some ideas to pass the dynamically creating values to Store procedure through SQL parameters?

I want to save those values into table where that table is also created dynamically.




Thanks

Siva

推荐答案

这并不是很容易，因为SQL没有数组的任何概念.
但是，您可以将它们作为定界字符串传递，并在过程中将其分解为临时表:
将列表作为字符串参数传递:

It''s not really very easy, since SQL does not have any concept of arrays.
However, you could pass them through as a delimited string, and break it into a temporary table in your procedure:
Pass a list as a string parameter:

DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
CREATE TABLE #tempTab (id int not null)
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO #tempTab (id) VALUES (@VALUE)
END
SELECT * FROM myTable WHERE id IN (SELECT id FROM #tempTab)
DROP TABLE #tempTab

很久以前我编写了一个过程，该过程采用XML字符串，然后使用传递的XML查找真实数据.也许可以做到.

同样，使用XML与上述解决方案也没有什么不同，因此您也可以使用它.该解决方案是使用我们自己的格式来构造数据，并且使用XML也会做到这一点.

Long back I wrote a procedure that was taking XML string and then using the passed XML to find real data. Perhaps that could do.

Also using the XML is not so different from the above solution, so you could use that too. That solution is structuring data using our own format and using XML will also do the same.

这篇关于当值的数量未知时，如何通过SQL参数传递值列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！