Programming Samples

Click here to go to:

Excel VBA
MS Access
T-SQL
SSIS
SSRS
Crystal Reports
C# Code
ASP .NET Code
Oracle PL/SQL
Database Diagramming

Back to Home Page

SQL Dynamic T-SQL using sp_executesql

Dynamic T-SQL to retrieve data

This tutorial shows how to use Dynamic SQL to construct a query using variables and return a variable for use within the code.

Dynamic T-SQL Case Background

I needed a SQL job to use a stored procedure in a "maintenance" database where I could pass other database nameson the same SQL Server to perform a series of tasks each night. In addition, I also needed to extract information (in a variable) from the same table name that existed in each of the databases on the server. I could have created this stored procedure in each of the databases or come up with a solution in the "maintenance" database. I discovered that I could solve it using only the "maintenance" database by using dynamic sql with sp_executesql.

Creating and Testing the Dynamic T-SQL Code

1st attempt: The following code does not work as expected in "passing" a database name as a variable in the code

DECLARE @dbName varchar(500)
Declare @fullName varchar(1000)
Declare @id int

Set @dbName = 'AdventureWorks'
Set @id = 1
SELECT @fullName = FirstName + ' ' + LastName
from @dbName.Person.Contact
where ContactID = @id

select @fullName

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '.'.

2nd attempt: Use a simple EXEC statement on a varchar variable. While it successfully passes the database name in the code, it does not return the variable information required.

DECLARE @dbName varchar(500)
Declare @fullName varchar(1000)
Declare @id varchar(2)
Declare @sql varchar(5000)

Set @dbName = 'AdventureWorks'
Set @id = 1
set @sql = 'SELECT @fullName = FirstName + '' '' + LastName from ' +
@dbName + 'Person.Contact where ContactID = ' + @id

EXEC (@sql)
select @fullName

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@fullName".

(1 row(s) affected)

3rd attempt: Use sp_executesql with parameters to return the required variable back to the stored procedure. Syntax: sp_executesql @SQLstatement, @parameterInSQLstmt, @parameterOUT.

DECLARE @dbName varchar(500)
Declare @fullName nvarchar(1000)
Declare @id varchar(2)
Declare @sql nvarchar(4000)
Declare @parameter nvarchar(1000)
Declare @err int
DECLARE @CurrentName nvarchar(255)

Set @dbName = 'AdventureWorks'
Set @id = 1
SET @parameter = N'@fullName nvarchar(255) OUTPUT'

set @sql = 'SELECT @fullName = FirstName + '' '' + LastName
from '
+ @dbName + '.Person.Contact
where ContactID = '
+ @id

EXECUTE sp_executesql @sql, @parameter, @fullName=@CurrentName OUTPUT;
select @CurrentName as CurrentName;

CurrentName
-------------------------
Gustavo Achong

(1 row(s) affected)

By using dynamic SQL with sp_executesql, passing variables into and out from the query is easy.