 | |||
|
|
||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| Tutorial: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||
Global variables constitute a special type of variable. The server maintains the values in these variables. They carry information specific to the server or a current user session. They can be examined from anywhere, whether from a stored procedure or a batch. In the SQL Server 2005 documentation, Microsoft refers to them as scalar functions, meaning that they return just one value. Since you can still find references to global variables in some documentation and since I would like to use some of them in this chapter, I will review them both here and in the next chapter, which is dedicated to functions. Global variable names begin with an @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them. Let's review the principal global variables/scalar functions.
@@identity
Create table Eq (EqId int identity(1,1),
Make varchar(50),
Model varchar(50),
EqTypeId int)The @@identity global variable allows you to get the last identity value generated in the current session. It is important to read the value as soon as possible (that is, in the next Transact-SQL statement). Otherwise, it might happen that you initiate, for example, another stored procedure or a trigger that inserts a record to a different table with an Identity column. In such a case, SQL Server overwrites the number stored in @@identity with the new value. In the following example, a record will be inserted and a new identifier will immediately be read:
Declare @intEqId int
Insert into Eq(Make, Model, EqTypeId)
Values ('ACME', 'Turbo', 2)
Select @intEqId = @@identity
If one Transact-SQL statement inserts several records into a table with an Identity column, @@identity will be set to the value from the last record:
Declare @intEqId int Insert into Equipment(Make, Model, EqTypeId) Select Make, Model, EqTypeID From NewEquipment Select @intEqId = @@identity You will use this function very often. One of the most common types of stored procedures that you will write will just insert a record and return its new key to the caller.
@@error
This global variable is the foundation of all methods for error handling in the Microsoft SQL Server environment. It is essential to examine the value of this variable before any other Transact-SQL statement is completed, because the value of @@error will be reset. Even if the next statement is only a simple Select statement, the value of the @@error variable will be changed after it. In the following example, let's assume that an error will occur during the Update statement. @@error will contain the error code only until the next statement is executed; even the command for reading the @@error value will reset it. If it was completed successfully, SQL Server will set @@error to 0. The only way to preserve the @@error value is to immediately read it and store it in a local variable; then it can be used for error handling.
Update Equipment Set EqTypeId = 3 Where EqTypeId = 2 Select @intErrorCode = @@error If it is necessary to read more than one global variable immediately after a statement, all such variables should be included in a single Select statement:
Declare @intEqId int,
@intErrorCode int
Insert into Equipment(Make, Model, EqTypeId)
Values ('ACME', 'Turbo', 2)
Select @intEqId = @@identity,
@intErrorCode = @@Error
The @@error variable will be set to an error number only in the case of errors, not in the case of warnings. Supplementary information that the server posts regarding errors or warnings (that is, severity, state, and error messages) are not available inside a stored procedure or a batch. Only the error number is accessible from a stored procedure or a batch. Further components of error messages can be read only from the client application. You will find more details about use of the @@error function in the "Error Handling based on @@Error" section in Chapter 6.
@@rowcount
select Make, Model, EqTypeid into OldEquipment from Equipment where EqTypeid = 2 if @@rowcount = 0 Print "No rows were copied!"
NOTE Rowcount_big() is a function introduced in SQL Server 2000. It returns the number of affected records in the form of a bigint number.
TIP
 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 |
|
|
||
|
|||||||
