Although, not a recommended practice, but sometimes we have to write our queries using dynamic SQL. In such situations, it is generally needed to fetch the result (scalar or tabular) of dynamic SQL into the main (non-dynamic) query. This is not straight forward because dynamic SQL runs in its own scope and we cannot access the variables defined in main query. This post presents a few approaches to consume the result of a dynamic SQL query:
sp_ExecuteSql stored procedure
This is the most generic and powerful method of invoking dynamic SQL since it allows us to write a parameterized dynamic query with input/output parameters. Here’s a simple example of using sp_executesql to consume the result of a dynamic SQL query:
declare @today datetime
exec sp_executesql
N'Select @internalVariable = GetDate()', --dynamic query
N'@internalVariable DateTime output', --query parameters
@internalVariable = @today output --parameter mapping
select @today
Table variables and Temporary tables
This method is used when we want to get a tabular result set from our dynamic query. Here’s an example to get the result of a dynamically created SQL query by using table variables:
declare @myTable table
(
DatabaseName nvarchar(256),
DatabaseID int,
CreateDate datetime
)
insert into @myTable
exec (N'select name, database_id, create_date from sys.databases') --dynamic query
select * from @myTable
Here’s the same example that uses a temporary table to fetch the result set of a dynamic SQL query:
create table #myTable
(
DatabaseName nvarchar(256),
DatabaseID int,
CreateDate datetime
)
insert into #myTable
exec (N'select name, database_id, create_date from sys.databases') --dynamic query
select * from #myTable
drop table #myTable
Since temporary tables have physical existence so we can refer to the temporary table inside our dynamic SQL query as well. Here’s an example illustrating this technique:
create table #myTable
(
DatabaseName nvarchar(256),
DatabaseID int,
CreateDate datetime
)
--dynamic query
exec sp_executesql
N'insert into #myTable
select name, database_id, create_date from sys.databases'
select * from #myTable
drop table #myTable
Temporary tables or Table variables can also be used to fetch the result of a stored procedure. Notice that for saving this result, the columns of table variable/temporary table must match with the result of stored procedure. That is, we need to take “ALL” the columns. Here’s an example that grabs the result set from a stored procedure into a table variable.
declare @myTable table
(
ServerName nvarchar(256),
NetworkName nvarchar(256),
Status nvarchar(4000),
ID int,
Collation nvarchar(256),
ConnectTimeOut int,
QueryTimeOut int
)
insert into @myTable
exec sp_helpserver
select * from @myTable
Also, here’s an example to get result of a stored procedure using temporary table:
create table #myTable
(
ServerName nvarchar(256),
NetworkName nvarchar(256),
Status nvarchar(4000),
ID int,
Collation nvarchar(256),
ConnectTimeOut int,
QueryTimeOut int
)
insert into #myTable
exec sp_helpserver
select * from #myTable
drop table #myTable
Thats all from me. Let me know if you have any more solutions.
