Execute SQL dynamically in stored procedures and get return results

Long time no write stored procedure, because the stored procedure violates the OOP spirit, say with the parameter of the stored procedure, if the business demand changes, change the stored procedure is really quite troublesome. So now more inclined to be responsible for the business logic in the business layer to deal with, the business layer dedicated to doing business layer of things.

The dynamic execution of SQL statements solves the problem by intelligently wrapping a layer.

ALTER procedure [dbo]. [Proc_nextsteps] (@Countint, @IDS NVARCHAR ( -), @Rules NVARCHAR (MAX), @IDSOut NVARCHAR ( -) out) asBEGINSet@Rules =UPPER (@Rules);Set@IDSOut ="'Declare @indexintSet@index =1; while (@index<=@Count) Begindeclare @SplitRule NVARCHAR (MAX), @ValueintSelect@SplitRule =dbo. F_splitofindex (@Rules,'|', @index)if(CHARINDEX ('SELECT', @SplitRule) =1) begindeclare @sql nvarchar ( +)Select@sql =n'Select @Value = ('[Email protected]+')'exec sp_executesql @sql, N'@Value int out', @Value outendelse IF (CHARINDEX ('proc_', @SplitRule) =1) beginexec @Value=@SplitRuleENDELSEBEGINSet@Value =@SplitRuleENDif(@Value >=1) BeginSelect@[email protected]+dbo. F_splitofindex (@IDS,'|', @index) +'|'EndSet@[email protected]+1Endif(@IDSOut <>"') BeginSet@IDSOut =left (@IDSOut, LEN (@IDSOut)-1) EndEnd

declare @res nvarchar (1000)
EXEC [dbo]. [Proc_nextsteps] 3, ' id1|id2|id3 ', ' SELECT COUNT (1) from dbo. a_cdkey| Proc_b|0 ', @res out
Print @res

Execute SQL dynamically in stored procedures and get return results