Local variables

SQL Server 2005 recognizes only local and table variables. This book except outlines operations such as declaring variables, assigning values with the select, set, or update statement; and displaying values. Also find some workarounds for potential problems.

Variables 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...

By submitting your personal information, you agree to receive emails regarding relevant products and special offers from TechTarget and its partners. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

considered to be functions.

Local Variables The scope of local variables is a batch (a set of T-SQL statements that is sent to SQL Server and executed simultaneously). This restriction implicitly includes a single stored procedure (because stored procedures are defined in a batch). This is a significant limitation. However, several workarounds can be used as solutions to this problem. A stored procedure cannot access variables defined in other stored procedures. One way to pass values to and from stored procedures is to use parameters. Keep in mind that you are passing only the values associated with the variables, not references, as you can in some other programming languages. Another way to transfer value between stored procedures or between batches is the use of more permanent database objects such as tables or temporary tables. Let's review basic operations with local variables.

Declaring Variables Before you can do anything with a local variable, you need to declare it. Declaration consists of the reserved word Declare and a list of variables and their respective data types. The names of variables must comply with the rules for identifiers with one exception—they must begin with @:

Declare @LastName varchar(50)

It is possible to define several variables in a single Declare statement. You just need to separate them with commas:

NOTE One stored procedure or a batch can have up to 10,000 local variables.

You can define variables based on user-defined data types:

Declare @OfficePhone phone

NOTE You cannot define the nullability of the variable, as you can with table columns. This does not mean that variables cannot contain null values. In fact, before assignment, the value of each variable is null. It is also possible to explicitly set the value of each variable to null.

Assigning Values with the Select Statement There are several ways to assign a value to a local variable. In early versions of SQL Server, the only way to do this was to use a modification of the Select statement:

Select @LastName = 'Smith'

It is also possible to assign several variables in the same statement:

NOTE It is necessary to assign a value of an appropriate data type to the variable; however, there are some workarounds. In some cases, the server will perform an implicit conversion from one data type to another. SQL Server also includes a set of functions for explicit conversion. Convert() and Cast() can be used to change the data type of the value (see Chapter 4). Some data types are not compatible, so explicit conversion is the only solution.

Quite often, variables are assigned values from the result set of the Select statement:

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? If more than one record is returned in the result set, a variable will be assigned the values from the last record. The only trouble is that we cannot predict which record will be the last, because this position depends on the index that the server uses to create the result set. It is possible to create workarounds to exploit these facts (that is, to use hints to specify an index or use minimum and/or maximum functions to assign extreme values). The recommended solution, however, is to narrow the search criteria so that only one record is returned. The other behavior that might cause unexpected results is the case in which a result set does not return any records. It is a common belief and expectation of many developers that the variable will be set to null. This is absolutely incorrect. The content of the variable will not be changed in this case. Observe the following example, or try to run it against the Asset5 database:

Only if the values of the variables were not previously set will they continue to contain a null value. The variable can be assigned with any Transact-SQL expression such as a constant or a calculation, or even a complete Select statement that returns a single value:

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:

Assigning Values with the Set Statement In SQL Server 7.0, the syntax of the Set statement has been expanded to support the assignment of local variables. In earlier versions, it was possible to use the Set statement only to declare cursor variables. Today, Microsoft is proclaiming this as a preferred method for assigning variables:

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). There is just one problem with the Set statement—it is not possible to assign several values with one statement. You will be forced to write code like this:

Set @Make = 'ACME'
Set @Model = 'Turbo'
Set @EqType = 'cabadaster'

Assigning Values in the Update Statement The ability to set the values of local variables in an Update statement is a feature that is buried deep in the oceans of SQL Server Books OnLine. It is an element that was designed to solve concurrency issues when code needs to read and update a column concurrently:

E-Handbook

0 comments

E-Mail

Username / Password

Password

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy