CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
amateur
amateur

Reputation: 44605

execute stored procedures returned from database table

I am working with sql server 2008

I have a database table that has a column containing a stored procedure name.

I want to query the database table which returns a list of the stored procedure names, and execute them.

The stored procedures are similar all having a select statment. The data returned in this select statement I want to insert in to a data base table.

Pseudo code looks like this:

INSERT INTO MyTable
EXECUTE sp_executesql SELECT StoredProcedureName FROM Table

Anyone able to assist me with correct sql for achieveing the above?

Upvotes: 2

Views: 799

Answers (2)

AJP
AJP

Reputation: 2125

This is pretty much same as @Coltech answer just with cursor.

    DECLARE @spname VARCHAR(200)
    DECLARE @sql VARCHAR(1000)

    DECLARE your_cursor CURSOR FOR 
    SELECT spname
    FROM yourTable;

    OPEN your_cursor;

    FETCH NEXT FROM your_cursor 
    INTO @spname;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'EXEC ' + @spname

        execute sp_executesql @sql

        FETCH NEXT FROM your_cursor 
        INTO @spname;
    END
    CLOSE your_cursor;

Upvotes: 1

Coltech
Coltech

Reputation: 1710

sp_executesql accepts a unicode string not a tsql statement. So you would need to execute your procedure(s) like this:

execute sp_executesql 'execute ' + @storedprocedurename

which will execute a single procedure.

You will need to write some iterative process to populate the @storedprocedurename variable from your source table.

Upvotes: 1

Related Questions