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)

About these ads

31 thoughts on “SQL SERVER – DECLARE Multiple Variables in One Statement

  1. 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

    Like

  2. 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.

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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?

    Like

  7. 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

    Like

  8. 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’

    Like

  9. Pingback: SQL SERVER – T-SQL Constructs – Declaration and Initialization – SQL in Sixty Seconds #003 – Video « SQL Server Journey with SQL Authority

  10. Pingback: SQL SERVER – T-SQL Constructs – *= and += – SQL in Sixty Seconds #009 – Video « SQL Server Journey with SQL Authority

  11. 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.

    Like

  12. 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

    Like

  13. hi pinal,
    i have one problem with my query.it will work fine but i want suppose i will add some columns in table then the query will be automatically change means in this query i don’t want use hard core values as i use.Is any way for that.

    Thanks,
    Datta

    Like

Leave a Reply

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