How to EXECUTE a set of stored procedures in a table

Question

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()
)

Answer 1

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;