Thursday, July 24, 2008

SQL (reminder): Executing a Stored Procedure Dynamically with SP_EXECUTESQL

I've been doing SharePoint too long if I need a reminder on how to do this...

If you, for some reason that I will not go into here, have a table cell with the name of a stored procedure, along with the parameters for said procedure, and you wish to execute that procedure, below is the general format:

Stored Procedure Name and parameter values:

MySP 1, 'a', 'foo', 'New Jersey'

Valid SQL to execute:

EXEC SP_EXECUTESQL 'EXEC MySP 1, ''a'', ''foo'', ''New Jersey'''

SP_EXECUTESQL does not allow inline string manipulation (such as prepending the EXEC statement or escaping the single quotes), so that manipulation has to be done before executing the string.

Thanks to Andre for the reminder.



Post a Comment

<< Home