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 ‘

About these ads

4 Comments

Filed under SQL

4 responses to “Assigning Query results to a SQL Variable

  1. sqlbeginner

    This is however fine, I want to do this dynamically by using exec on dynamically built SQL string. The example and the error is follows:
    Using adventureworks on SQL server 2005

    declare @sql nvarchar(max);
    set @sql = null;
    select @sql = ‘Begin’ + CHAR(10);
    select @sql = @sql +’declare @num bigint;’ + CHAR(10);
    select @sql = @sql + ‘Select @num = department_id from HumanResources.Department where name =’ + ”’Engineering”’ + ‘;’+ CHAR(10);
    select @sql = @sql + ‘End;’ + char(10);
    print @sql
    exec @sql

    on executing the string, I get the following error

    Msg 2812, Level 16, State 62, Line 8
    Could not find stored procedure ‘declare @num bigint;
    Select @num = department_id from HumanResources.Department where name =’Engineering’;
    ‘.

    Kindly advise.

    • There are a couple of things that I would change in your query. First there are two things in the the fifth line

      1.) Line 5: department_id --> may need to be changed to --> DepartmentId (It's like that in my table)
      2.) Line 5: name may need to be enclosed in brackets i.e. [name]
      3.) Line 8: exec @sql should read exec(@sql)

      So your code should be something like:
      declare @sql nvarchar(max);
      set @sql = null;
      select @sql = 'Begin' + CHAR(10);
      select @sql = @sql +'declare @num bigint;' + CHAR(10);
      select @sql = @sql + 'Select @num = DepartmentID from HumanResources.Department where [Name] =' + '''Engineering''' + ';'+ CHAR(10);
      select @sql = @sql + 'End;' + char(10);
      print @sql
      exec (@sql)

      Hope that's what you are needing.

  2. sqlbeginner

    Thanks, the code string executed fine, however I am curious to know as to how @sql enclosed in brackets worked fine and the same goes with name coulmn as well.

    • Glad to hear it worked out.

      I suggested the ‘name’ column to be enclosed in brackets because I think it’s a keyword (similar to a reserved word in other programming languages) and without the brackets, you may get an error.

      Regarding the parentheses surrounding @sql, I think exec() is a function that requires parentheses. So that may just be part of the syntax.

Comment on this

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s