Web Hosting Java Blog

340 Part I EXAM PREPARATION 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.

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

340 Part I EXAM PREPARATION 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.

For high quality java hosting services please check java web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 339 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.

For high quality java hosting services please check tomcat web hosting website.

338 Part I EXAM PREPARATION 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 state ments 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

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 337 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

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

NOTE 336 Part I EXAM PREPARATION 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 state- Picking Up the Trash Garbage ment, how to put different types of data into variables using variable Collection is the term computer sci-types, and how variables get destroyed. Now it s time to learn how to ence folks use to describe what hap- use the variables. pens to variables after they die. In many languages (C, C++, and others), the programmer has to deal with issues, such as heap fragmentation Setting and Using Variables and memory management from creat- There are four ways to put a value into a variable. If you need to put ing and disposing of variables. SQL data into a variable that comes out of a SELECT statement, you can Server handles all these mechanics do something like this: internally, so you don t have to worry about them. SELECT @id = id FROM sysobjects WHERE name = syscolumns

For reliable and cheap web hosting services please check javaweb hosting website.

NOTE 336 Part I EXAM PREPARATION 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 state- Picking Up the Trash Garbage ment, how to put different types of data into variables using variable Collection is the term computer sci-types, and how variables get destroyed. Now it s time to learn how to ence folks use to describe what hap- use the variables. pens to variables after they die. In many languages (C, C++, and others), the programmer has to deal with issues, such as heap fragmentation Setting and Using Variables and memory management from creat- There are four ways to put a value into a variable. If you need to put ing and disposing of variables. SQL data into a variable that comes out of a SELECT statement, you can Server handles all these mechanics do something like this: internally, so you don t have to worry about them. SELECT @id = id FROM sysobjects WHERE name = syscolumns

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

NOTE 336 Part I EXAM PREPARATION 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 state- Picking Up the Trash Garbage ment, how to put different types of data into variables using variable Collection is the term computer sci-types, and how variables get destroyed. Now it s time to learn how to ence folks use to describe what hap- use the variables. pens to variables after they die. In many languages (C, C++, and others), the programmer has to deal with issues, such as heap fragmentation Setting and Using Variables and memory management from creat- There are four ways to put a value into a variable. If you need to put ing and disposing of variables. SQL data into a variable that comes out of a SELECT statement, you can Server handles all these mechanics do something like this: internally, so you don t have to worry about them. SELECT @id = id FROM sysobjects WHERE name = syscolumns

For high quality website hosting services please check java web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 335 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.)

For high quality website hosting services please check java web hosting website.

334 Part I EXAM PREPARATION 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

For high quality java hosting services please check tomcat web hosting website.