Execute with list/array?

I have a stored procedure that I want to execute 1-10 times with a set of variables

I want to feed in a comma seperated list...and execute the stored procedure for each varchar(10) item in the list.
2 examples

Declare @myVar varchar(100)
Set @myVar = 'set1,set2,set3'

My SP would then execute 3 time
exec mySP  'set1'
exec mySP  'set2'
etc...
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Luis PérezSoftware Architect in .NetCommented:
First, create the SplitString function with this script:

CREATE FUNCTION [dbo].[SplitString] (@sep nvarchar(10), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2))
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 8000 END) AS s
    FROM Pieces
  )


With this function working, you can easily split any string and operate with its separated values (for example, with a cursor):

--Create a variable with comma-delimited values and fill it
DECLARE @commaDelimitedVariables varchar(1000)
SET @commaDelimitedVariables = 'var1,var2,var3'

--Declare a table variable to hold the separated values
DECLARE @tableVariables TABLE (s VARCHAR(100))

--Insert the separated values (calling the SplitString function)
INSERT INTO @tableVariables SELECT s FROM dbo.SplitString(',',@commaDelimitedVariables)

--This variable "myVar" is to hold each one of the separated values inside the loop
DECLARE @myVar VARCHAR(100)

--Declare a cursor to iterate thru the values
DECLARE myCur CURSOR FOR SELECT s FROM @tableVariables
OPEN myCur
--Get the first value
FETCH NEXT FROM myCur INTO @myVar
--If there is value, enter the loop
WHILE @@FETCH_STATUS = 0
BEGIN
        EXEC mySP @myVar
      --PRINT @myVar
        --Get the next value
      FETCH NEXT FROM myCur INTO @myVar
END
--Close the cursor and free resources
CLOSE myCur
DEALLOCATE myCur

Hope that helps.
0
 
idmedellinCommented:
Use something like that
Declare @myVar varchar(100)
Declare @pos1 int
Declare @pos2 int
Declare @param varchar (30)

Set @myVar = 'seta1,set2,set3' 
set @pos1 = 1
set @pos2 = PATINDEX ( '%,%' , @myVar )

while @pos1 <= @pos2
  begin
     set @param = substring (@myVar, @pos1, @pos2 -1)
     exec mySP @param
     set @myVar = substring (@myVar, @pos2 + 1, len (@myVar))
     select @param, @myvar, @pos1, @pos2
     set @pos2 = PATINDEX ( '%,%' , @myVar )
  end
     exec mySP @myvar

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.