How to Assign SQL Query Results to a Variable in TSQL

Learn how to effectively assign SQL query results to a variable in TSQL, ensuring correct execution and retrieval of values.
---
This video is based on the question https://stackoverflow.com/q/75880911/ asked by the user 'jvr' ( https://stackoverflow.com/u/15173778/ ) and on the answer https://stackoverflow.com/a/75882002/ provided by the user 'siggemannen' ( https://stackoverflow.com/u/13061224/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Assign the results of SQL query to variable in TSQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Assign SQL Query Results to a Variable in TSQL

When working with TSQL, you may encounter situations where you need to assign the results of a SQL query to a variable for further processing. This is commonly a requirement in dynamic SQL scenarios. In this guide, we’ll walk you through a specific problem and provide you with a detailed, step-by-step solution to effectively achieve your goal.

Understanding the Problem

Imagine you have a scenario where you want to fetch a SQL query stored in one variable and then execute that query to assign its result to another variable. Here’s a simplified breakdown of what you’re trying to accomplish:

You have an SQL statement saved in a variable (let's call it @ sqlquery).

You want to execute that SQL statement but retrieve its outcome into another variable (say @ countresult).

If not done correctly, the execution may only display results in a tabular format rather than capturing the value in a variable.

Given SQL Code Example

The process begins with the generation of the SQL query and its assignment to a variable as demonstrated below:

[[See Video to Reveal this Text or Code Snippet]]

The Next Step

Now the goal is to execute the query held by @ sqlquery and capture the result into @ countresult. Here’s the problematic code you might find:

[[See Video to Reveal this Text or Code Snippet]]

In this case, the query executes, but the output is displayed in a table rather than being assigned to @ countresult.

Solution Overview

The main issue in your approach is how you are trying to assign the output of an aggregate function (like COUNT(*)) directly into a variable. The solution involves wrapping your query in another SELECT statement to ensure its result is assigned correctly.

Suggested Approach

Instead of attempting to execute the query directly as you did initially, you should modify your SQL to wrap it. Here’s how the final, corrected SQL implementation should look:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Solution

Dynamic SQL Construction: The initial part of the code retrieves the SQL statement from your database and assigns it to @ sqlquery.

Wrapping the Query: Instead of calling EXEC directly on @ sqlquery, you wrap it in a SELECT statement to ensure that the output is captured:

For example, the SQL becomes: SET @ sqlquery = 'SELECT @ countresult = (' + @ sqlquery + ')'.

Executing to Capture Output: By executing this new dynamic SQL, the output of the query now correctly stores in the @ countresult variable.

Final Output Display: Finally, you can print or use the @ countresult as needed.

Conclusion

Assigning results from SQL queries to variables in TSQL can be tricky, especially when dealing with dynamic SQL. However, by wrapping your queries properly, you can control the flow of data and ensure that your variables receive the intended values.

If you find yourself writing complex dynamic SQL in TSQL, consider this approach. It not only helps in accurately capturing outputs, but also gives you greater control over your database interactions. Happy querying!