In my previous blog post: Suggestion: “USE” Keyword with Linked Servers, I talked about the suggestion I’ve posted on Microsoft Connect which allows easy execution of dynamic code on linked servers.

I’ve received a surprising reply from Umachandar of the SQL Programmability team, revealing to me an amazing feature that I wasn’t aware of until now.

Here’s the sample that he wrote:

DECLARE
@ServerName VARCHAR(200),
@Statement NVARCHAR(MAX),
@Parameters NVARCHAR(MAX)
SET @ServerName = 'SQLINS1';
SET @Parameters = N'@Path NVARCHAR(4000)';
SET @Statement = N'BACKUP DATABASE [MyDB] TO DISK = @Path'
DECLARE @sp nvarchar(1000) = QUOTENAME(@ServerName) + N'.[MyDB].sys.sp_executesql'
EXEC @sp @Statement, @Parameters, @Path

Apparently there’s a little-known method for executing stored procedures (this is from Books Online):

-- Execute a character string
{ EXEC | EXECUTE }
( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
[ AS { LOGIN | USER } = ' name ' ]
[;]

In SQL Server Books Online it’s not clear right away that you can actually use this method to execute stored procedures which you’ve built their name dynamically, and even pass on parameters to them like any other stored procedure.

Though there is a tiny little example that shows you can actually do it:

DECLARE @proc_name varchar(30);
SET @proc_name = 'sys.sp_who';
EXEC @proc_name;

Yeah, not very informative, and very easy to miss.

This feature grants us amazing power and protection from SQL Injection.

We can use this feature for:

• Executing dynamic code at linked servers where the linked server name is dynamic as well (my first example).
• Executing a stored procedure with a dynamic name provided externally.
• Executing stored procedures on databases with a dynamic name.

And all the while we pass the parameters without SQL Injection!

More than that, the dynamic name itself is protected from SQL Injection because SQL explicitly looks for a stored procedure with the provided name, and doesn’t simply execute any command given:

-- This will obviously drop the table:
CREATE TABLE SQLInjectionTest1
(col1 INT)
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'DROP TABLE SQLInjectionTest1'
EXECUTE (@SQL)
/* -- result:
Command(s) completed successfully.
*/GO
-- This will NOT drop the table:
CREATE TABLE SQLInjectionTest2
(col1 INT)
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'DROP TABLE SQLInjectionTest2'
EXECUTE @SQL
/* -- result:
Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'DROP TABLE SQLInjectionTest2'.
*/GO
DROP TABLE SQLInjectionTest2

I’m surprised this feature is not more widely known because it can be extremely useful at times.

I’m really curious, were you familiar with this feature before? Let me know in the comments below!

The post Dynamic Stored Procedure Execution in SQL Server appeared first on Madeira Data Solutions.