DECLARE Single, Multiple Variables In SqlServer.

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values with either a SET or SELECT statement.

In SQL Server 2008 when variables are declared they can be assigned values as well.

Syntax

DECLARE

    {{ @local_variable [AS] data_type }

        | { @cursor_variable_name CURSOR }

        | { table_type_definition }

    } [ ,...n]

< table_type_definition > ::=

    TABLE ( { < column_definition > | < table_constraint > } [ ,... ]

            )

< column_definition > ::=

    column_name scalar_data_type

    [ COLLATE collation_name ]

    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed, increment ) ] ]

    [ ROWGUIDCOL ]

    [ < column_constraint > ]

< column_constraint > ::=

    { [ NULL | NOT NULL ]

    | [ PRIMARY KEY | UNIQUE ]

    | CHECK ( logical_expression )

    }

< table_constraint > ::=

    { { PRIMARY KEY | UNIQUE } ( column_name [ ,... ] )

    | CHECK ( search_condition )

    }

1-      Use DECLARE with Single variables

This example uses a local variable named @find to retrieve author information for all authors with last names beginning with Ring.

USE pubs

DECLARE @find varchar(30)

SET @find = 'Ring%'

SELECT
          au_lname,
          au_fname,
          phone

FROM
          authors

WHERE
          au_lname LIKE @find

2-      Use DECLARE with two variables

This example retrieves employee names from employees of Binnet & Hardley (pub_id = 0877) who were hired on or after January 1, 1993.

USE pubs

SET NOCOUNT ON

GO

DECLARE @pub_id char(4), @hire_date datetime

SET @pub_id = '0877'

SET @hire_date = '1/01/93'

-- Here is the SELECT statement syntax to assign values to two local  variables.

-- SELECT @pub_id = '0877', @hire_date = '1/01/93'

SET NOCOUNT OFF

SELECT
          fname,
          lname

FROM
          employee

WHERE
          pub_id = @pub_id and hire_date >= @hire_date

 

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)

Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2