Assigning Query results to a SQL Variable

You are at this post because you have created a variable in a TSql script and would like to assign it a value. You would like to assign this value dynamically to this SQL Server Variable. For example, perhaps you want to assign the average price of a customer purchase to a variable, or you would like to assign the count of all the rows in a particular query to an SQL variable so that you can use it later in the script.

Below is an example on how to count all the rows in a Products table, assign it to a query named @ProductCount and then print the results to our SQL Server Management Studio Message tab (via the print statement – optional). As always, if you have any questions, please don’t hesitate to post your questions or comments.

DECLARE @ProductCount int
SELECT @ProductCount = COUNT(*) FROM SalesLT.Product
PRINT ‘THERE ARE ‘ + CAST(@ProductCount AS VARCHAR(20)) + ‘ ROWS IN THE TABLE ‘