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