6

I frequently see the syntax

Declare @var int;
Set @var = 1;

But you can set the variable at declaration time

Declare @var int = 1;

Is there a gotcha to the latter option? Am I missing something that is internal or doesn't work in all cases where you should not declare and set the variable in the declaration?

I ask because of subtitle gotcha's that I hear which I would not expect. Like Aaron Bertrand lower casing data types which is something that msdn also does not state but has been found in the community.

  • 2
    SQL Server 2005 and previous did not support this. It's a hold over from when you couldn't do it and most people get into a rhythm, then don't change. You'll also see this is vendor code everywhere because they haven't updated their code since 2000 <cough>. – Sean says Remove Sara Chipps Feb 20 '17 at 23:16
  • It has nothing to do with not changing code, it is backward compatibility. A minute thing to make two lines of code into one is not worth the time spent going back through all the development phases for a release. – user507 Feb 21 '17 at 1:47
  • @ShawnMelton I hope new development is not "targeting" 2005 which is out of support or holding it for "backwards compatibility". Now, whether it is worth changing the code or not is another question. These are also the same people using TEXT/NTEXT, etc. – Sean says Remove Sara Chipps Feb 21 '17 at 3:00
2

It was not possible until SQL Server 2008. Before this version you should declare the variable, and then set its value.

According MS DOCS:

THIS TOPIC APPLIES TO:
SQL Server (starting with 2008)
Azure SQL Database
Azure SQL Data Warehouse
Parallel Data Warehouse

  • 6
    Just FYI, all the documentation now states "Starting with 2008" - this one is just a coincidence. Look at ROW_NUMBER() and MIN(), for example. Obviously these functions existed before SQL Server 2008. This was a shortcut by the documentation team that I think was ill-advised, but that's a different question. – Aaron Bertrand Feb 21 '17 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.