How do I assign a dynamic query result to a variable in SQL Server?

How do I assign a dynamic query result to a variable in SQL Server?

Getting result of dynamic SQL into a variable for sql-server

1. DECLARE @sqlCommand nvarchar(1000)
2. DECLARE @city varchar(75)
3. SET @city = ‘London’
4. SET @sqlCommand = ‘SELECT COUNT(*) FROM customers WHERE City = @city’
5. EXECUTE sp_executesql @sqlCommand, N’@city nvarchar(75)’, @city = @city.

How do you assign a value to a variable in a SELECT statement in SQL?

To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.

How to assign value to variable in dynamic SQL?

In the sp_executesql you need to pass the parameter definition and then parameter mappings. When you Execute a Query String, It is Considered as a separate session, so The variables that you have declared in the current windows won’t be accessible whereas you can access the Temporary tables. So you have to declare the variable inside the string.

How to store the result of dynamic SQL?

Here is the script which declares additional parameter which is Name and returns value into it. FROM [AdventureWorks2014]. [HumanResources]. [Department] 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.

How to execute dynamic T-SQL statement in SQL Server?

You can use sp_executesql in order to execute dynamic T-SQL statement and initialize SQL variables. In the sp_executesql you need to pass the parameter definition and then parameter mappings.

Which is an example of a dynamic SQL?

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. FROM [AdventureWorks2014].