CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresdynamic-sqlexecute
sahibeast
sahibeast

Reputation: 351

How to EXECUTE a set of stored procedures in a table

I have a table that stores names of stored procedures in my file structure. The idea is that the calling stored procedure will be given @in_Strings as a parameter; these are the display names of the stored procedures I want to execute.

Then, I want to search my table of stored procedures and execute the ones whose display names match with the inputted set.

For example, the calling stored procedure may be given an input set of strings 'foo', 'bar', and 'baz'. That means I want to be able to execute 'dbo.foo_sproc','dbo.bar_sproc', and 'dbo.baz_sproc'.

I am using SQL Server 2012. Any ideas?

BTW, my stored procedures table looks like this:

CREATE TABLE dbo.SPROCS
(
    Display_Name NVARCHAR(256) NOT NULL,
    SPROC_Name NVARCHAR(256) NOT NULL,
    CreatedDateTime DATETIME2(2) NOT NULL DEFAULT GETUTCDATE()
)

Upvotes: 2

Views: 418

Answers (3)

bobobobo
bobobobo

Reputation: 67224

I wanted to call a stored procedure on every primary key field in a table.

In MySQL, cursors look as follows, derived from the example here:


DELIMITER //

-- To iterate over a table, use a cursor
CREATE PROCEDURE runProcForEveryKey()
modifies sql data
BEGIN
  -- how we know to stop iterating
  DECLARE done bool DEFAULT FALSE;

  -- we have to select the value the cursor points at into
  -- some normal variable
  DECLARE cId int;

  -- cursors are a lot like stl iterators.
  DECLARE curs CURSOR FOR SELECT primaryKeyId FROM Table;

  -- when the cursor reaches the end of the table,
  -- we set this 'done' variable
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- put the cursor at the beginning of the table
  OPEN curs;
  
  -- here's our for loop
  read_loop: LOOP
    FETCH curs INTO cId;
    IF done THEN
      LEAVE read_loop;
    END IF;
    
    CALL someOtherStoredProc( cId );
  END LOOP;

  CLOSE curs;
END //



DELIMITER ;

Upvotes: 0

crthompson
crthompson

Reputation: 15865

A sql server example:

A cursor can loop through your result set and execute your stored procedures.

This (simple, but untested) script loops every sproc in the @in_strings variable.

CREATE PROCEDURE getSprocs @in_Strings varchar(max)
AS
BEGIN
    DECLARE @sql varchar(max)
    DECLARE db_cursor CURSOR FOR  
    SELECT SPROC_Name
    FROM dbo.SPROCS
    where sproc_name in (@in_Strings)

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name   

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        set @sql = 'exec ' + @name
        exec @sql
        FETCH NEXT FROM db_cursor INTO @name   
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor
END

Upvotes: 2

Andriy M
Andriy M

Reputation: 77657

You can use dynamic SQL, like in paqogomez's answer, but, unlike any other kind of names, procedure names can be parametrised in SQL Server, and so dynamic SQL is not necessary in this case.

For instance, if this was about a single procedure, you could read and execute the matching name using this simple method:

DECLARE @SPROC_Name nvarchar(256);

SELECT @SPROC_Name = SPROC_Name
FROM dbo.SPROCS
WHERE Display_Name = @in_string;

EXECUTE @SPROC_Name;

Just like that.

Since you are mentioning a set of strings, however, you will need a cursor to loop through the result set of matching procedure names and pass each name to the EXECUTE statement.

DECLARE @SPROC_Name nvarchar(256);

DECLARE procnames CURSOR
    LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
    SELECT @SPROC_Name = SPROC_Name
    FROM dbo.SPROCS
    WHERE ... /* condition involving Display_Name and @in_string */
;

OPEN procnames;
FETCH NEXT FROM procnames INTO @SPROC_Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE @SPROC_Name;
    FETCH NEXT FROM procnames INTO @SPROC_Name;
END;

CLOSE procnames;
DEALLOCATE procnames;

Upvotes: 1

Related Questions