Dynamic Stored Procedure Execution in SQL Server

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:

Transact-SQL

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

1

2

3

4

5

6

7

8

9

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

Transact-SQL

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

1

2

3

4

5

-- 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: