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)
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
Same goes for assigning variables from a query
Select @Var1 = Col1, @Var2 = Col2 From Table …
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.
Can we undeclare variables? We can deallocate CURSOR varilable, how can we deallocate or undeclare local variable?
Thanks
J
There is no need and no way to do it
Use GO as a batch seperator so that variables would lose the scope
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
Probably you may need
select Name=lastname firstname from emp
UNION ALL
SELECT firstname from emp
select lastname + ‘ ‘ firstname as Name from emp
Thanks for providing such useful information.
Himadri
Thanks for the tutorial.Is it possible to deallocate local variable?
Can we declare and initialize variables in one line
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
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
What do you want to do with a cursor?
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
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?
Is there any performance increase by using single declaration for multiple variables !
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
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’
DECLARE @Var1 INT, @Var2 INT
SET @Var1 = 1
SET @Var2 = 2
if @Var1=@Var2
print ‘these are equal’
else
print ‘these are not equal’
how to assign variable in where condition
You cannot assign value to variables in where condition. You can only use it for comparison in the where clause
variable_name datatype [NOT NULL := value ];
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.
hi,
is this all relevant to DBA or for developers? pls confirm.
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