CODEWITHSUNDEEP
sql-server
user3109653
user3109653

Reputation: 351

Declaring Local Variables in SQL Server 2012

I have a question about local variables in SQL Server. When I declare local variables, I begin with a DECLARE statement and then create my variables. Can I then just begin typing other statements? Do I need anything like a BEGIN statement before I do anything else?

For example is this sample of coding acceptable:

DECLARE
   @stock INT,
   @customername

select @stock = ...
select @customername ...

Can I follow this with any other statement? Do I have to separate the declare section from the rest of my coding?

Upvotes: 0

Views: 380

Answers (3)

avb
avb

Reputation: 1753

You do not have to separate this.
But the declaration you wrote is wrong should be: 

DECLARE @stock INT; 
DECLARE @customername NVARCHAR(11);

All variables must be typed.

The difference is that if you havae select statement returning more than one row then:
set @var = (select value from table) would raise an error.
select @var = value from table would assign to @var value from last row selected.

Upvotes: 1

dean
dean

Reputation: 10098

No, it's just a matter of your coding style. You can freely mix DECLAREs with other code. Just be sure to declare the variable before using it, and not declaare it twice inside the same batch. Uninitiallized variables are usually NULL. It is possible to declare and initialize variable in a single statement:

DECLARE @stock INT = 123;

BTW, there is a difference between SELECT @stock = ... and SET @stock = ...

Upvotes: 0

tekrat
tekrat

Reputation: 115

Yes you can.

BEGIN is used to section of a block of code for special operations. BEGIN can be used to create a transactional query. For example:

BEGIN TRAN
Update MyTable SET X = 1 WHERE Y='4'
ROLLBACK TRAN -- Didn't like what I did, undo it

Until you ROLLBACK or COMMIT your transaction all the tables your query in the block are in limbo. Your server can't do much until you release your code.

Upvotes: 0

Related Questions