Post reply

Execute stored procedures dynamically

  • March 5, 2015 at 1:11 pm

    #315171

    It might be silly I'm doing but checking to see an effective solution!!

    I have a column in a table, which have the stored procedure name stored in each row. Now, I need to execute each SP in the table dynamically. I'm trying to construct a SQL but not able to fire them!!

    DECLARE @sql VARCHAR(MAX)

    SELECT @sql = STUFF((SELECT '; GO EXEC ' + StoredProcedureName + '' FROM MyTable FOR XML PATH ('')),1,5,'')

    print @sql

    EXEC sp_executesql @sql

    Can anyone please advise, what I missed here? Thanks in advance.

  • March 5, 2015 at 1:18 pm

    #1782028

    Never mind posting. It seems I need to declare NVARCHAR to fix the issue;

    DECLARE @sql NVARCHAR(MAX)

    SELECT @sql = STUFF((SELECT ';EXEC ' + StoredProcedureName + '' FROM MyTable FOR XML PATH ('')),1,1,'')

    print @sql

    EXEC sp_executesql @sql

  • March 5, 2015 at 3:35 pm

    #1782053

    Correct. sp_ExecuteSQL requires NVARCHAR.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply