?
Solved

Execute with list/array?

Posted on 2011-04-26
4
Medium Priority
?
298 Views
Last Modified: 2012-05-11
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...
0
Comment
Question by:lrbrister
4 Comments
 
LVL 25

Accepted Solution

by:
Luis Pérez earned 2000 total points
ID: 35466879
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
 
LVL 9

Expert Comment

by:radcaesar
ID: 35466886
0
 
LVL 5

Expert Comment

by:idmedellin
ID: 35466909
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
 

Author Closing Comment

by:lrbrister
ID: 35468384
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question