Persistence of Local Variables

How long is “a while”?

Local variables last for the duration of the batch. A batch is a set of commands that are sent to the server, compiled, and executed together. Batches can be separated in ISQL by the GO command. Consider this example:

declare @msg varchar(30)
select @msg = “This is a message.”
select @msg
go
select @msg
go

------------------------------
This is a message.

Msg 137, Level 15, State 2
Must declare variable ‘@msg’.

The @msg variable lasts only for the duration of the batch in which it is declared. Once that batch is complete, the memory set aside for the variable is reclaimed by the server. The first batch is sent to the server. The client waits for the results of this batch to come back, and the second batch is sent. This second batch knows nothing about the declared @msg variable in the first batch.

---

Note:  If you return messages from your procedures, you should return them as PRINT statements, not as SELECT statements. A SELECT statement, like the one used here, creates a separate result set. Some programs handle each result set differently. PRINT statements are understood to mean, “This is an informational or error message.” Most programs take special action to process PRINT statements.

---

Assigning Values in an UPDATE Statement

Variables may be set in an UPDATE statement, when table values are adjusted. This can be useful if you need to update a column in a table, read the value of another column in the same row, and you need to be certain that the row does not change between the time you updated and the time you read the row.

Prior to MS SQL Server v6.0, this work was done inside a transaction.

This example updates a mythical inventory table, deducting five widgets and reading the original quantity in a single statement. The table was defined in some examples on Day 9.

declare @qty smallint
update  inventory
set     qty = qty - 5,
        @qty = qty
here    inv_id = 438

This is more efficient than updating the table and selecting from it inside a transaction. This form of UPDATE requires no extra work to assign a value to the variable—the page had to be read anyway to change it.

You might use a statement like this to update an inventory table and take action if it turned out that you don’t have enough inventory to satisfy demand. You might do it this way if insufficient inventory were a relatively rare event, to maximize performance of the most common occurrence.

Note that I defined quantity as a smallinteger. The inventory table I’m using as an example comes from the Table Creation lesson on Day 9. That table defined qty as a smallint. You must be sure to match data types when assigning values to variables. If you don’t, the server will convert the table data into the variable’s type. This may cause undesirable side effects, such as loss of precision or truncated character data.

DECLARE Syntax

Here is the syntax statement for the DECLARE statement, when used to declare local variables:

declare @<varname> <datatype>
       [, @<varname> <datatype> ...]

Global Variables

Global variables are used on SQL Server to communicate information to the client. Global variables are read-only. They are set automatically by the server. Global variables have two @ signs before their names.

This command gets the current version of the SQL Server:

select @@version

-----------------------------------------------------
Microsoft SQL Server  6.50 - 6.50.201 (Intel X86)
      Apr  3 1996 02:55:53
      Copyright (c) 1988-1996 Microsoft Corporation

Global variables communicate important information inside stored procedures and triggers. The most important of these is the @@error variable. When you write stored procs or triggers, every time a table is changed, check @@error. If there is an error, you must undo changes and take some kind of error-handling action. On Day 14, I’ll use @@error inside transactions to see if problems occurred, and if they did, I’ll rollback the transaction and abort the SQL batch.