SQL SERVER – Results of Dynamic SQL Into A Variable

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.

SQL SERVER - Results of Dynamic SQL Into A Variable variablestore

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.

Solarwinds
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 = @ID

Now 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 ReturnedName

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

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Optimize Key Lookup by Creating Index with Include Columns
Next Post
SQL SERVER – Finding User Who Dropped Database Table

Related Posts

4 Comments. Leave new

  • Kalman Kernerman
    December 21, 2018 11:16 pm

    Very cool tip !!!

    Reply
  • I am glad you liked it.

    Reply
  • 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

    Reply
  • Thank you!!!! It helped me

    Reply

Leave a Reply

Menu