问题描述
我有一个使用动态 sql 的存储过程,它根据传递给它的值更新几列.我正在尝试测试它的多个值,而无需手动输入这些值.这些值将从表格中获取.有没有办法在表中传递所有这些值并让它通过 proc?就像在常规编程语言中一样,您将遍历数组.我在 sql server 2012 中这样做.
I have a stored proc using dynamic sql that updates a few columns based on the value passed to it. I am trying to test it out for multiple values without having to enter those manually. These values are to be taken from a table. Is there a way to pass all these values in the table and have it go through the proc? Just like in your regular programming language where you would run through an array. I am doing this in sql server 2012.
代码是这样的
CREATE PROCEDURE sp1 @enteredvalue int AS BEGIN UPDATE table1 SET column1 = 'some var char value', column2 = 'some integer values' WHERE xid = @enteredvalue END
我想从具有不同值的表中输入该整数参数 (@enteredvalue) 的值.
I want to enter the values for that integer parameter (@enteredvalue) from a table that has different values.
推荐答案
也许更动态的 SQL 可以解决问题(连同解析器)
Perhaps a little more dynamic SQL will do the trick (along with a parser)
Declare @String varchar(max) = '1,25,659' Declare @SQL varchar(max) = '' Select @SQL = @SQL + concat('Exec [dbo].[sp1] ',Key_Value,';',char(13)) From (Select * from [dbo].[udf-Str-Parse-8K](@String,',')) A Select @SQL --Exec(@SQL)
退货
Exec [dbo].[sp1] 1; Exec [dbo].[sp1] 25; Exec [dbo].[sp1] 659;
如果需要,UDF(超快!)
The UDF if needed (super fast!)
CREATE FUNCTION [dbo].[udf-Str-Parse-8K](@String varchar(8000), @Delimiter varchar(50)) Returns Table As --Usage: Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',') -- Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||') -- Select * from [dbo].[udf-Str-Parse-8K]('The quick brown fox',' ') Return ( with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)), cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a, cte1 b, cte1 c, cte1 d) A ), cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter), cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S) Select Key_PS = Row_Number() over (Order By A.N) ,Key_Value = Substring(@String, A.N, A.L) ,Key_Pos = A.N From cte4 A )