The other day during Comprehensive Database Performance Health Check, I was asked if there is a way to store results of dynamic SQL into a variable. This is a very popular question and let us see the answer to this question in this blog post.
For example here is the dynamic SQL which I am executing with the help of sp_executeSQL. When you run the procedure, you get the necessary results. In our case, we are seeing a result string as a Marketing which is the name of the department with ID = 4.
DECLARE @sqlCommand NVARCHAR(4000)
DECLARE @ID INT
SET @ID = 4
SET @sqlCommand = 'SELECT [Name]
FROM [AdventureWorks2014].[HumanResources].[Department]
WHERE DepartmentID = @ID'
EXEC sp_executesql @sqlCommand, N'@ID INT', @ID = @IDNow the question is how to get the value of the column [Name] into a variable.
Here is the very easy trick for the same. Here is the script which declares additional parameter which is Name and returns value into it.
DECLARE @sqlCommand NVARCHAR(4000)
DECLARE @ID INT
DECLARE @Name NVARCHAR(100)
SET @ID = 4
SET @sqlCommand = 'SELECT @Name = [Name]
FROM [AdventureWorks2014].[HumanResources].[Department]
WHERE DepartmentID = @ID'
EXEC sp_executesql @sqlCommand, N'@ID INT, @Name NVARCHAR(100) OUTPUT',
@ID = @ID, @Name = @Name OUTPUT
SELECT @Name ReturnedNameYou can use this script and example as a template for your need where you want to run dynamic SQL and store the result of it into a variable.
If you have any such script, I request you to share the same with me and I will publish it with due credit to you. Please always use sp_executesql instead of just EXEC to run your dynamic SQL.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
10 Comments. Leave new
Very cool tip !!!
I am glad you liked it.
I use dynamic SQL a lot, but I do it slightly different. The parameter @ID isn’t passed to the SP, it is built into the @sqlCommand as text and then executed.
DECLARE @sqlCommand NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RESULT nvarchar (255)
SET @ID = 4
SET @sqlCommand = ‘SELECT [Name] as ReturnedName
FROM [AdventureWorks2014].[HumanResources].[Department]
WHERE ID = ‘ + cast (@ID as nvarchar (10))
exec sp_executesql @sqlCommand, N’@RESULT nvarchar (255) out’, @RESULT out
or (I prefer the table variable because of simpler and more readable syntax)
DECLARE @sqlCommand NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RESULT Table (RESULT nvarchar (255))
SET @ID = 4
SET @sqlCommand = ‘SELECT [Name] as ReturnedName
FROM ViewAdressen
WHERE ID = ‘ + cast (@ID as nvarchar (10))
insert @RESULT exec sp_executesql @sqlCommand
select Result as ReturnedName from @RESULT
Thank you!!!! It helped me
Thank you! This one was very helpfull to me.
Perfect. Thanks for such a valuable tutorial.
Thank you so much for this.
In my case I needed a dynamic DLOOKUP type feature as follows: (I am an amateur so please feel free to correct)
— This procedure will return a single value form any specified field in any specified table using criteria in any specified field or a debug message.
Input parameters are TB – table name, FN – lookup field, SF – search field and IV – search criteria.
— =============================================
CREATE PROCEDURE [dbo].[stpGetOneValue]
— Add the parameters for the stored procedure here
@TB nvarchar(50),
@FN nvarchar(50),
@SF nvarchar(50),
@IV nvarchar(50),
@OutStr varchar(50) output
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
DECLARE @MyStatement nvarchar(MAX)
DECLARE @Out nvarchar(50)
DECLARE @COut nvarchar(50)
DECLARE @InputValue nvarchar(50)
DECLARE @FieldName nvarchar(50)
DECLARE @Table nvarchar(50)
DECLARE @SearchField nvarchar(50)
DECLARE @MyCount int = 0
SET @Table = @TB
SET @FieldName = @FN
SET @InputValue = @IV
SET @SearchField = @SF
SET @MyStatement = ‘SELECT @FieldVal = ‘ + @FieldName + ‘ FROM ‘ + @Table + ‘ WHERE ‘ + @SearchField + ‘ = ”’ + @InputValue + ””
EXEC sp_executesql @MyStatement, N’@FieldName nvarchar(50), @InputValue nvarchar(50), @Table nvarchar(50), @SearchField nvarchar(50), @FieldVal nvarchar(50) OUTPUT’,
@InputValue = @InputValue, @FieldName = @FieldName, @Table = @Table, @SearchField= @SearchField, @FieldVal = @Out OUTPUT
SELECT @OutStr = @Out
— Trash reslut if more or less than a single value is returned (this is NOT a recordset procedure)
SET @MyStatement = ‘SELECT @MyCount = COUNT (@FieldName) FROM ‘ + @Table + ‘ GROUP BY ‘ + @SearchField + ‘ HAVING ‘ + @SearchField + ‘ = ”’ + @InputValue + ””
EXEC sp_executesql @MyStatement, N’@FieldName nvarchar(50), @InputValue nvarchar(50), @Table nvarchar(50), @SearchField nvarchar(50), @MyCount int OUTPUT’,
@InputValue = @InputValue, @FieldName = @FieldName, @Table = @Table, @SearchField= @SearchField, @MyCount = @COut OUTPUT
if @COut 1
SELECT @OutStr = ‘Stored procudure did not return a unique record’
END
Thanks for the posting, I did try exact same things except the return output variable is type INT, then I got below error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 [Batch Start Line 0]
Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.
Can i know why ? Thank you.
hi, i send datas to this stored procedure by using API,i have a problem about Dynamic tables, i could not get id of my recorded data as output.pls help me to solve this problem
ALTER PROC Experience
@Subject1 INT
,@Subject2 INT
,@TableNumber INT
,@IDR INT OUTPUT
AS
BEGIN
DECLARE @CMDS VARCHAR(MAX);
DECLARE @Subj1 VARCHAR(10);
SET @Subj1=CONVERT(varchar(10),@Subject1);
DECLARE @Subj2 VARCHAR(10);
SET @Subj2=CONVERT(varchar(10),@Subject2);
DECLARE @TableNames VARCHAR(100);
SET @TableNames=’TableNo_’+CONVERT(varchar(50),@TableNumber);
SET @CMDS=’INSERT INTO ‘+@TableNames+’ (@Subject1,@Subject1) VALUES(”’+@Subj1+”’,”’+@Subj2+”’);’
EXEC (@CMDS);
SELECT @IDR = SCOPE_IDENTITY();
END
how about , if the query returning a json / xml how to catch result xml / json in to nvarchar(max) variable?