Pass several sets of parameters to run stored proc

,


    This procedure will let you execute existing stored procedure with several sets of multiple parameters
    like: exec _ParamSplit_SP 'ColorSP', "258,'RED';367,'BLUE';125,'GREEN'",";"
    it will be the same as
    exec ColorSP 258,'RED'
    exec ColorSP 367,'BLUE'
    exec ColorSP 125,'GREEN'

Created for SQL 7

if exists (select name from sysobjects 
	where name = '_ParamSplit_SP' AND type = 'P')
	drop procedure _ParamSplit_SP
GO
create proc _ParamSplit_SP
	@Sp_Name varchar(30),@ParamStr Varchar(8000),@Delim char(1) =','
AS
DECLARE @lCurrValue		Varchar(200),
	@liCounter		Int,
	@lPos			Int,
	@lLen			Int,
	@lDelimStr		varchar(5),
	@currParamStr		Varchar(8000)

SET NOCOUNT ON
select @currParamStr = @ParamStr
select @liCounter = 1
select @lDelimStr = '%'+ @Delim +'%'
select @lPos= patindex(@lDelimStr,@currParamStr)

while @lPos<>0
	BEGIN
		select @lLen = len(@currParamStr)
		select @lCurrValue= ltrim(substring(@currParamStr,1,@lPos-1))
		select @currParamStr =  substring(@currParamStr,@lPos+1,@lLen)
		select @lPos= patindex(@lDelimStr,@currParamStr)
		select @liCounter = @liCounter +1
		exec ( @Sp_Name +' '+ @lCurrValue )
		
	END				
select @lCurrValue = ltrim(@currParamStr)
exec ( @Sp_Name +' '+ @lCurrValue)

SET NOCOUNT off
Return

Rate

Share

Share

Rate