Thursday, May 7, 2009

Inline variable initialization in SQL Server 2008

This is one of the cool features that I like in sql server 2008. We can initialize the variable inline.

DECLARE @i int = 10 ;

Seems to be a simple, but it saves a 'Select /Set' statement

You can initialize the variable with any constant including the @@ROWCOUNT /@@SPID/ SCOPE_IDENTITY() etc.


 

SELECT 1

UNION
SELECT 2

UNION
SELECT 3

DECLARE @i INT
=@@ROWCOUNT

SELECT @i --- 3

GO


 

SELECT 1

UNION
SELECT 2

UNION
SELECT 3

UNION
SELECT 3

DECLARE @i INT
=@@ROWCOUNT

SELECT @i --- 3

GO


 

DECLARE @testTab Table (i int
identity, j int
)

INSERT
INTO @testTab

SELECT 1

UNION
SELECT 2

UNION
SELECT 3

UNION
SELECT 3

DECLARE @i INT
=SCOPE_IDENTITY()

SELECT @i --- 3

GO

0 comments:

Post a Comment