SQL SERVER - results of dynamic SQL in a variable

Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.

SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.

REQUEST COMPLIMENTARY SQLS*PLUS LICENCE

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

9 September 2020

Is there a way to save dynamic SQL results in a variable? This is a very popular question, let’s see the answer to that question in this blog.

For example, here is dynamic SQL which we execute with sp_execute SQL. When you run a procedure, you get the results you need. In our case, we see the result line as Marketing, which is the name of the department with ID = 13.

DECLARE @sqlCommand NVARCHAR(4000)
DECLARE @ID INT
SET @ID = 13
SET @sqlCommand = 'SELECT [Name]
FROM [AndreyExWorks2018].[HumanResources].[Department]
WHERE DepartmentID = @ID'
EXEC sp_executesql @sqlCommand, N'@ID INT', @ID = @ID

Now the question arises on how to get the value of the column [Name] in a variable.

Here is a very simple trick for the same thing. Here is a script that announces an additional parameter, which is Name, and returns the value to it.

DECLARE @sqlCommand NVARCHAR(4000)
DECLARE @ID INT
DECLARE @Name NVARCHAR(100)
SET @ID = 13
SET @sqlCommand = 'SELECT @Name = [Name]
FROM [AndreyExWorks2018].[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 save the result in a variable.

If you have such a script, we ask you to share it and we will publish it with due respect. Always use sp_executesql instead of EXEC to run dynamic SQL.