As im looking at Table Variables vs Temp Tables and other alternates I thought I would a post a start on @Local_Variables to get me started, below is some syntax and examples of uses for @Local_Variables

Notes:

Only usable in the Batch operator that they are DECLARED in
Can be defined by either SET or SELECT

/* Create local variable */
DECLARE
@var int
SET
@var = 200

/* Alternate Create Local Variable (Set the Variable as you create it instead of using SET) */
DECLARE @Var INT = 200

/* Create multiple Variables at the same time */
DECLARE
@var INT ,
@var2 INT ,
@var3 VARCHAR(MAX)
SET
@var = 200
SET
@var2 = 1
SET
@var3 = 'This is a text var'

/* Create and set multiple vars at the same time */
DECLARE
@var INT = 200 ,
@var2 INT = 1 ,
@var3 VARCHAR(MAX) = 'This is a text var' 

/* SET VS SELECT */
/* It is possible to populate variables with both SET and SELECT */
-- SET
DECLARE
@var INT
SET
@var = 200
-- SELECT
DECLARE
@Var2 int
SELECT
@var2 = count(*) FROM sys.databases 

-- Using local variable within Batch it was created
DECLARE
@var INT = 200
SELECT
@var
GO
/* Trying to use local variable outside of the original batch it was created */
DECLARE
@var INT = 200
GO
-- closes batch operation that has the @Var in
SELECT
@var
GO
Advertisements