Working with VariablesA 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:
The first thing to understand is how variables are named. Naming VariablesA 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 VariablesA 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 TypesYou 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 ScopeAll 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 VariablesThere 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 VariablesGlobal 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
User OptionsThe @@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 @@ROWCOUNTWhat'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? |