 | |||
|
|
||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| Tutorial: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Variables in Transact-SQL are the equivalent of variables in other programming languages, but due to the nature of the Transact-SQL language, their use and behavior are somewhat different. SQL Server 2005 (and 2000) documentation recognizes only local variables and table variables. Documentation in SQL Server 7 and earlier versions was also referring to global variables. In SQL Server 2005 (and 2000) global variables are considered to be functions.
Local Variables
Declaring Variables
Declare @LastName varchar(50) It is possible to define several variables in a single Declare statement. You just need to separate them with commas:
Declare @LastName varchar(50),
@FirstName varchar(30),
@BirthDate smalldatetime
NOTE You can define variables based on user-defined data types:
Declare @OfficePhone phone
NOTE
Assigning Values with the Select Statement
Select @LastName = 'Smith' It is also possible to assign several variables in the same statement:
Select @LastName = 'Smith',
@FirstName = 'David',
@BirthDate = '2/21/1965'
NOTE Quite often, variables are assigned values from the result set of the Select statement:
Select @Make = Equipment.Make,
@Model = Equipment.Model,
@EqType = Equipment.EqType
From EqType INNER JOIN Equipment
ON EqType.EqTypeId = Equipment.EqTypeId
Where EqId = 2
There are some potential problems associated with this approach. How will the server assign values if the result set contains multiple records, or no records?
Declare @make varchar(50),
@model varchar(50),
@EqType varchar(50)
Select @Make = 'ACME',
@Model = 'Turbo',
@EqType = 'cabadaster'
Select @Make = make,
@Model = Model,
@EqType = EqType.EqType
From EqType INNER JOIN Equipment
ON EqType.EqTypeId = Equipment.EqTypeId
Where EqId = -1
Select @make make, @model model, @EqType EqType
Since the Equipment table does not have a record with the identifier set to –1, the variables will keep their original values.
make model EqType ----------------------- -------------------------- -------------- ACME Turbo cabadaster (1 row(s) affected)
Only if the values of the variables were not previously set will they continue to contain a null value.
Select @Make = Make,
@Model = Model,
@EquipmentName = Make + ' ' + Model,
@EqType = (select EqType
from dbo.EqType EqType
where EqType.EqTypeId = Equipment.EqTypeId)
From dbo.Equipment
Where EqId = 2
There is one combination of statements and expressions that will result in a syntax error. It is not possible to return a result set from the Select statement and to assign a variable in the same Select statement:
Select Make,
@Model = Model -- wrong
From dbo.Equipment
Where EqId = 2
Assigning Values with the Set Statement
Set @LastName = 'Johnson'
Use of the Set statement is preferable, since it makes code more readable and reduces the opportunity to make a mistake (assign a variable and return a result set at the same time).
Set @Make = 'ACME' Set @Model = 'Turbo' Set @EqType = 'cabadaster'
Assigning Values in the Update Statement
Update Inventory Set @mnsCost = Cost = Cost * @fltTaxRate Where InventoryId = @intInventoryId
Displaying the Values of Variables
Select @LastName Print @FirstName It is possible to include a local variable in a result set that will be returned to the user:
Select make "Selected make",
Model "Selected Model",
@Model "Original model"
From Equipment
Where EqId = 2
 The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||
Home: 
|
|
 |  |  | |  | |  | |  | |  | |  | |  | |  | |
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
