SQL SERVER – DECLARE Multiple Variables in One Statement

Just a day ago, while I was enjoying mini vacation during festival of Diwali I met one of the .NET developer who is big fan of Oracle. While discussing he suggested that he wished SQL Server should have feature where multiple variable can be declared in one statement. I requested him to not judge wonderful product like SQL Server with just one feature.

SQL Server is great product and it has many feature which are very unique to SQL Server. Regarding feature of SQL Server where multiple variable can be declared in one statement, it is absolutely possible to do.

Method 1: Multiple statements for declaring multiple variables

DECLARE @Var1 INT
DECLARE
@Var2 INT
SET
@Var1 = 1
SET @Var2 = 2
SELECT @Var1 'Var1', @Var2 'Var2'
GO

Method 2: Single statements for declaring multiple variables

DECLARE @Var1 INT, @Var2 INT
SET
@Var1 = 1
SET @Var2 = 2
SELECT @Var1 'Var1', @Var2 'Var2'
GO

From above example it is clear that multiple variables can be declared in one statement. In SQL Server 2008 when variables are declared they can be assigned values as well. Please refer my previous article for the same SQL SERVER – 2008 – Two Convenient Features Inline Assignment – Inline Operations.

Reference : Pinal Dave (http://www.SQLAuthority.com)

,
Previous Post
SQLAuthority News – Download Microsoft SQL Server Management Pack for Operations Manager 2007
Next Post
SQL SERVER – Stored Procedure WITH ENCRYPTION and Execution Plan

Related Posts

31 Comments. Leave new

  • Method 3: Single statements for declaring and defining multiple variables:

    DECLARE @Var1 INT, @Var2 INT
    SELECT @Var1 = 1, @Var2 = 2
    SELECT @Var1 ‘Var1’, @Var2 ‘Var2’
    GO

    Reply
  • Same goes for assigning variables from a query

    Select @Var1 = Col1, @Var2 = Col2 From Table …

    Reply
  • Thanks for the tutorial.

    I have a table like this:

    TagKey TagID TagvALUE TagTimeStamp

    I want to create this constraint:

    “If the previous TagValue = current TagValue then do not update”.

    Can you help me, please?. Thanks.

    Reply
  • Can we undeclare variables? We can deallocate CURSOR varilable, how can we deallocate or undeclare local variable?

    Thanks
    J

    Reply
    • There is no need and no way to do it

      Use GO as a batch seperator so that variables would lose the scope

      Reply
  • I want display like under from

    select Name=lastname+’ ‘+firstname from emp

    but all firstname should be left align like under one column.

    Thanks
    Rajesh

    Reply
  • Thanks for providing such useful information.

    Himadri

    Reply
  • Binay TIwari
    July 27, 2010 5:51 pm

    Thanks for the tutorial.Is it possible to deallocate local variable?

    Reply
  • Can we declare and initialize variables in one line

    Reply
  • Hi all,
    Im using cursor in my procedure,Im getting the value from 1 table,that value is stored in local variable @var,that value is the column name of another table.I want to pass the @var in Cursor Select Statement.How i do that..the Below statements im using…

    Set @var=(select field from table1)

    Declare cursorname cursor for
    Select @var from table2 where No=2 —>Getting Error
    open cursorname

    Reply
  • Hi
    i want to use cursor but in sql server 2005 there is no %table-type%,we have to create individual field and set into
    fetch statement and even we can not get field like cursorName.FieldName in sql server like orale.

    Send Me Reply if you have any alternate to cursor.

    Thanking You

    Hemant Patel

    Reply
  • Greeting Dave and All.

    Although an older post, this and many other posts still referenced frequently, so I wanted to add the following exception to the above.

    From BOL, when declaring *table variables* specifically, those must be the only variable declared. A trivial limitation imho, but one to be aware of.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-2017

    Carl Hager

    Reply
  • Hi to all,
    I wanted to ask, whether there is any difference between declaring multiple variables in 1 statment and declaring multiple variables in multiple statments (1 in each statement).

    I mean, is there any noteworthy difference from the point of view of performance, etc. Is any one better than the other for any reason?

    Reply
  • rama chandra sahu
    September 12, 2011 3:45 pm

    Is there any performance increase by using single declaration for multiple variables !

    Reply
  • HOW TO USE LOCAL VARIABLE INSTEAD OF SELECT STATEMENT IN CURSOR ?

    I CAN USE IT IN ORACLE.

    LIKE THIS;

    SET @STRSQL= ‘SELECT A FROM TABLE’

    OPEN cur FOR @STRSQL

    Reply
  • is there any way to compare these variable,

    like

    DECLARE @Var1 INT, @Var2 INT
    SET @Var1 = 1
    SET @Var2 = 2
    SELECT @Var1 ‘Var1’, @Var2 ‘Var2’, @Var1 = @Var2 ‘Compare’

    Reply
    • DECLARE @Var1 INT, @Var2 INT
      SET @Var1 = 1
      SET @Var2 = 2
      if @Var1=@Var2
      print ‘these are equal’
      else
      print ‘these are not equal’

      Reply
  • how to assign variable in where condition

    Reply
    • You cannot assign value to variables in where condition. You can only use it for comparison in the where clause

      Reply
  • variable_name datatype [NOT NULL := value ];

    Reply
  • Srinivasan K
    June 26, 2012 1:44 pm

    select @l_path1=setting_value from settings where setting_id=215
    select @l_path2=setting_value from settings where setting_id=216

    Need both the value , through single select statement.

    Reply
  • hi,

    is this all relevant to DBA or for developers? pls confirm.

    Reply
  • Hi,
    Can you tell me (if it is possible) how to define an array type variable?
    An example would be…
    I have a stored procedure in which want to filter on multiple codes (strings) but the qty of codes is variable (there may be 1,2,3 and so on).
    Many thanks
    Roger

    Reply

Leave a Reply