Working with Variables


Working with Variables

A variable is a temporary place to put pieces of data that you're working with. This can include things such as dates, numbers for counting with, or strings that you need to manipulate. SQL Server is a language that could be termed "strongly typed"; that is, it has several different types of variables, and there are certain restrictions on converting from one type to another. With respect to variables, you need to understand six topics:

  • Naming variables

  • Declaring variables

  • Variable types

  • Variable scope

  • Setting and using variables

  • Global variables

The first thing to understand is how variables are named.

Naming Variables

A variable follows the same naming scheme as a column name , with one slight addition. One of the big problems that SQL Server has is determining which part of a statement is a variable and which is a column name. If, for example, you have a variable that contains an object ID, and you want to find the name of the object, things might get very confusing:

 SELECT name FROM sysobjects WHERE id = id 

That statement returns all the rows in the sysobjects table (id is always equal to id, after all). That's probably not what you intended to do, but it's a common mistake. So, to allow SQL Server to make the connection between ID the column and ID the variable, you always have to put an at sign (@) in front of a variable name. So, the preceding statement becomes

 SELECT name FROM sysobjects WHERE id = @ID 

Now SQL Server can tell that what you really want is one specific row from the sysobjects table. This is, of course, assuming you declared the @ID variable. You didn't do that because you're not sure how to declare a variable? Well, it's time to learn.

Declaring Variables

A variable is declared using the handily named DECLARE statement. So, to declare a single variable with a type of int , you use something like this:

 DECLARE @id int 

That's pretty simple, so now try the advanced version:

 DECLARE @id int,         @foo int,         @fee varchar(30) 

The comma separating each of the variables is required by SQL Server. The fact that they're all on new lines and tabbed in is just for the sake of style. The following is an equivalent statement:

 DECLARE @id int,         @foo int,         @fee varchar(30) 

So, it's important to note that you can take any statement and break it onto multiple lines. Don't overdo it, but use this feature to make your scripts easier to read and maintain. In addition, it's easier to draw conclusions about what a variable does by what kind of data is in the variable, or what type the variable has, and putting the variables on different lines helps to emphasize their types.

Variable Types

You can create variables of nearly any type that you can store in a table, with the restriction that you can't create variables of type TEXT , NTEXT , or IMAGE . So, you can create int , tinyint , smallint , datetime , smalldatetime , uniqueidentifier , varchar , nvarchar , char , nchar , and so on. You can (and should) specify lengths where appropriate, such as varchar(30) or nchar(15) . You can create a string variable that holds up to 8,000 bytes, so you can build a varchar(8000) or an nvarchar(4000) in a variable. (Remember that the nchar and nvarchar types are double-wide characters , so they take up twice as much room as a varchar .)

There is also a special variable type called TABLE that can be used to store a recordset. For example, you can declare something like this:

 DECLARE @tmp TABLE (id int,                       tablename varchar(50)             ) INSERT INTO @tmp SELECT id, name FROM sysobjects WHERE type = 'u' 

That creates a table similar to a temporary table that is available only within the current batch. This is faster and requires fewer resources than a temp table, but with a more limited scope. Be aware, however, that this consumes SQL Server memory, so don't put exceptionally large tables into these structures. You also cannot use a local variable of type table as the target for something like this:

 INSERT INTO @tmp EXEC sp_foo 

You cannot use a variable of type table as the target of a SELECT..into statement, either. You can populate the table using only INSERT..SELECT , INSERT..VALUES , and UPDATE . The table automatically "goes away" at the end of the batch, which is the end of its scope.

Variable Scope

All variables cease to exist at the end of their scope. To keep things simple, SQL Server has only one scope for a variable, which is the local scope. (Yes, there is a section called "Global Variables." They don't count; you'll see why later in this chapter.) That means that when your script has a GO in it, any variables you have need to be redeclared and reset.

So, you now know how to create variables using the DECLARE statement, how to put different types of data into variables using variable types, and how variables get destroyed . Now it's time to learn how to use the variables.

NOTE

Picking Up the Trash "Garbage Collection" is the term computer science folks use to describe what happens to variables after they die. In many languages (C, C++, and others), the programmer has to deal with issues, such as heap fragmentation and memory management from creating and disposing of variables. SQL Server handles all these mechanics internally, so you don't have to worry about them.


Setting and Using Variables

There are four ways to put a value into a variable. If you need to put data into a variable that comes out of a SELECT statement, you can do something like this:

 SELECT @id = id FROM sysobjects WHERE name = 'syscolumns' 

After executing this, and assuming the variables are all declared properly, you'll end up with some number in the @id variable. That's one way to put a value into a variable. What's another way?

 SET @id = 42 

If the value you're putting into the variable does not come from a table, you can use the SET statement to put the value in. That includes things like this:

 DECLARE @today datetime SET @today=getdate() 

The getdate() function is one of the built-in SQL server functions; it returns the current date and time, which the SET statement places into the variable @today . You can set a variable to the value of any SQL Server function, provided the data returned by the function will fit into the variable you declare. So, although it's appropriate to do something like the datetime example above, doing something like this

 DECLARE @today datetime SET @today=substring('hello', 1, 3) 

doesn't work, because the substring function returns a string, not a date. The exact error message you get is Syntax error converting datetime from character string .

Another way to put a value into a variable is with the EXEC statement, like this:

 DECLARE @ReturnCode int EXEC @ReturnCode = sp_who 

This executes the sp_who stored procedure, which returns a result set, which is displayed, and a return code, which is not the result set. A return code is a status code that the stored procedure can use to describe whether there were any errors. Return codes are limited to datatype int , so you can also use return codes to return integer data. Chapter 9 covers this topic in detail. The final way to set a variable is also to use an EXEC , but with an output parameter. That's covered in Chapter 9 as well.

You need to know a few other things about setting and using variables. First of all, setting a variable to an arithmetic expression follows the classic arithmetic orders of operation. So, to set the variable @value to 14 , use this:

 SET @value = 2 + 3 * 4 

How does that work? T-SQL, and most programming languages, use what's called the standard order of operations , which is as follows: Any operations in parenthesis are done first, from the innermost set of parenthesis working outward. Then, any functions are evaluated. After that, multiplication and division are carried out, then addition and subtraction. So, to get a different result for the preceding expression, add parentheses:

 SET @value = (2 + 3) * 4 

This sets the variable @value to 20 .

Similar to the way a DECLARE statement works, you can create statements like this by delimiting the values in the SELECT :

 DECLARE @id int,         @name varchar(30) SELECT @id = id, @name = name FROM sysobjects WHERE sysstat = 113 

You should note three things here. First, both variables do get populated . Second, that result set contains multiple values (several records in sysobjects have a sysstat of 113). Which record puts values into the variables? It's non-deterministic . In other words, the values come out of one of the records, but there's no way to predict which record. So, writing code like that is probably a bad idea. Finally, the SELECT statement can do this multiple-assignment-in-one-statement trick, but the SET statement cannot.

Now, you've been setting these variables everywhere, but how do you know what's in them? There are two ways to display the contents of a variable. First, you can use the print statement, like this:

 DECLARE @id int,        @name varchar(30) SELECT @id = id, @name = name FROM sysobjects WHERE sysstat = 113 PRINT @id PRINT @name 

Or, you can do this:

 DECLARE @id int,        @name varchar(30) SELECT @id = id, @name = name FROM sysobjects WHERE sysstat = 113 SELECT @id, @name 

What's the difference? There are three major differences. First, the SELECT statement returns a rowset, whereas the PRINT statement just prints out a string. So, the PRINT statement can just put the string into the output stream, but the SELECT statement can be used by applications. Second, a PRINT statement doesn't necessarily happen in sequence with the rest of a batch, and actually, PRINT statements usually aren't executed until the end of the batch or if there's a PRINT statement pending and the value needs to be changed. This happens only with very long and complicated batches, but can make debugging difficult. Finally, a SELECT statement returns any data you want, whereas a PRINT can return only the data that can be converted into strings directly. You cannot use PRINT with a function call or with anything else that cannot be implicitly converted to a string type.

So, say that you have a variable with the string "42" in it. Not the number 42, but the string "42" . Here's an example:

 DECLARE @TestValue varchar(30),        @TestResult int SET @TestValue = '42' SET @TestResult = @TestValue + 3 PRINT @TestResult 

This code will print the value 45 , because it automatically converted the string value 42 to the integer value 42 and then added 3 . Now look at this example:

 DECLARE @TestValue varchar(30),        @TestResult int SET @TestValue = '42' SET @TestResult = @TestValue + '3' PRINT @TestResult 

This prints out the value 423 , because it takes the string 42 and adds the character 3 onto the end. To prevent confusion from these situations, it's best to use the convert function to convert values to the type for their result. In other words, do something like this:

 DECLARE @TestValue varchar(30),        @TestResult int SET @TestValue = '42' SET @TestResult = convert(int, @TestValue) + 3 PRINT @TestResult 

This produces the same result, but it's easier to figure out exactly what's going on because there's no ambiguity that it might return 423 or the number 45 . The plus sign is used for both string concatenation and addition.

Global Variables

Global variables are not variables. They are actually system functions that return various pieces of information about the current user environment for SQL Server. They are just called "functions" now, but we aren't going to do that here because the syntax is different, and it would be confusing to call two completely different constructs the same name. A global variable looks like a variable with two at-signs in front of it, as in @@CONNECTIONS , which returns the current number of user connections. You cannot declare global variables and you cannot directly change them with a SET or SELECT statement. So, if you use one of these special functions somewhere, realize that although it looks like a variable and can act like one, you can't assign any data to it; you can only read data from it. Table 6.1 provides a list of the global variables in SQL Server 2000:

Table 6.1. Global Variables in SQL Server 2000

Global Variable

Function

@@CONNECTIONS

Returns the current number of connections that applications currently have open to SQL Server.

@@CPU_BUSY

The time, in milliseconds , that SQL Server has been busy since the last time it was restarted.

@@CURSOR_ROWS

The number of rows that are in the previously opened cursor. If no cursor has been opened, it returns 0; if the cursor is asynchronous, it returns a negative number representing the number of rows that have been used so far; if the number is positive, it's the number of rows in the cursor; and if the value is -1, the cursor is dynamic, so there's no telling how many rows are in it. Cursors are covered in more detail later in this chapter.

@@DATEFIRST

Returns the number of the first day of the week. For example, if the first day of the week is set to Sunday, it returns 1, if it's set to Monday, it returns 2, and so on.

@@DBTS

Returns the current value of the timestamp for the database. This has absolutely no relation to system time, and is only used to manage certain inserts .

@@ERROR

Returns the error number for the last SQL statement executed.

@@FETCH_STATUS

Returns the status of the last cursor fetch operation: 0 for success, -1 for failure, or -2 for missing record.

@@IDENTITY

Returns the value used for the last INSERT INTO an identity column for the current connection.

@@IDLE

Returns the time in milliseconds that SQL Server has been idle since the last restart.

@@IO_BUSY

Returns the time in milliseconds that SQL Server has spent waiting for IO to return from a read or write request.

@@LANGID

Returns the language identifier of the current language in use.

@@LANGUAGE

Returns the name of the language currently in use, which is probably more useful than the ID number.

@@LOCK_TIMEOUT

Returns the number of milliseconds that the current connection will wait for a lock to clear to complete its work.

@@MAX_CONNECTIONS

The maximum number of simultaneous user connections allowed on the current SQL Server.

@@MAX_PRECISION

The precision used by the DECIMAL and NUMERIC data types on the server. By default, this is 38.

@@NESTLEVEL

The current nesting level during stored procedure execution. (See Chapter 9.)

@@OPTIONS

Returns an integer representing the settings of the user options for the current connection. (See "User Options," later in this chapter.)

@@PACK_RECEIVED

The number of network packets received by the SQL Server since it was last restarted.

@@PACK_SENT

The number of packets sent by the SQL Server since it was last restarted.

@@PACKET_ERRORS

The number of packet errors that the SQL Server has seen since it was last restarted.

@@PROCID

The stored procedure identifier of the currently executing stored procedure.

@@REMSERVER

Returns the name of the SQL Server running the remote stored procedure.

@@ROWCOUNT

Returns the number of rows returned by the last statement. (See "Using @@ROWCOUNT," later in this chapter.)

@@SERVERNAME

Returns the name of the current server.

@@SERVICENAME

Returns the name of the service under which SQL Server is running.

@@SPID

Returns the current process identifier used by SQL Server.

@@TEXTSIZE

The maximum number of bytes that will be returned in a result set to the current connection from selecting from a TEXT or IMAGE column.

@@TIMETICKS

The number of microseconds that occur in one tick of the computer's clock.

@@TOTAL_ERRORS

The total number of disk read/write errors that SQL Server has had since its last restart.

@@TOTAL_READ

The total number of physical disk reads that SQL Server has done since it was last started.

@@TOTAL_WRITE

The total number of physical disk writes that SQL Server has done since it was last started.

@@TRANCOUNT

This returns the number of transactions "deep" the current statement is in a nested transaction.

@@VERSION

Returns the version string (date, version, and processor type) for the SQL Server.

User Options

The @@OPTIONS variable uses a technique called bitmasking to return data. It's a good exercise in batch programming technique to see how to return specific data from the @@OPTIONS variable, because columns in some system tables use the same technique to store data.

To retrieve data from a bitmasked value, you need to have the map for how the value is laid out. The complete map for the value returned by the @@OPTIONS variable is found in SQL Server Books Online under the topic "User Options Option." In the table within that topic, the user option for ARITHIGNORE is 128 . So, to check whether the ARITHIGNORE flag is turned on, take the options variable and use the logical AND function to see whether the flag is set:

 DECLARE @UserOption int SET @UserOption = @@OPTIONS PRINT @UserOption & 128 

If this value returns 128 , then the flag was previously set, and ARITHIGNORE is turned on. If the value returns , then the flag was not set, and ARITHIGNORE is turned off. Other values that use this are the status value in the sysdatabases table and the status value in the sysobjects table.

Using @@ROWCOUNT

What's the fuss with @@ROWCOUNT ? It just returns the number of rows affected by the previous statement. Well, it can be a bit more complex than that. What would this do?

 UPDATE mytable SET emptype = 'manager' PRINT 'all done' PRINT @@ROWCOUNT 

This returns . Why? The PRINT statement does not change any rows. As a matter of fact, PRINT statements never return rows. If you want to use a rowcount, you must use it immediately after the row from which you want to pull the data. The following code doesn't work the way you'd expect, either:

 BEGIN TRANSACTION     UPDATE mytable SET emptype = 'manager' COMMIT TRANSACTION print @@ROWCOUNT 

The COMMIT TRANSACTION does not change or select any rows, so there's a zero value in @@ROWCOUNT . You'll learn more about BEGIN TRANSACTION and COMMIT TRANSACTION a little later in this chapter.

Now imagine that you are working on finishing your great masterpiece batch, but you need to go home and get some sleep. How are you going to remember where you left off when you come back in the morning?



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net