Programmable Objects Seminar 7

1. Programmable ObjectsSeminar 7 Deanne Larson

2. Variables • Variables allow you to store data values temporarily for later use in the same batch where they were declared. I describe batches later in the chapter, but for now, suffice it to say that a batch is one T-SQL statement or more sent to SQL Server for execution as a single unit. • Use a DECLARE statement to declare one or more variables, and use a SET statement to assign a value to a single variable. For example, the following code declares a variable called @i of an INT data type and assigns it the value 10: • DECLARE @i AS INT; SET @i = 10; • SQL Server 2008 introduces support for declaring and initializing variables in the same statement like so: • DECLARE @i AS INT = 10; • Throughout this chapter, I use separate DECLARE and SET statements so that you can also run the code in SQL Server 2005 and earlier versions.

3. Variables • When assigning a value to a scalar variable, the value must be the result of a scalar expression. The expression can be a scalar subquery. • For example, the following code declares a variable called @empname, and assigns it the result of a scalar subquery returning the full name of the employee with ID 3: • USE TSQLFundamentals2008; DECLARE @empname AS NVARCHAR(61); SET @empname = (SELECT firstname + N' ' + lastname FROM HR.Employees WHERE empid = 3); SELECT @empname AS empname;

4. Batches • A batch is one or more T-SQL statements sent by a client application to SQL Server for execution as a single unit. The batch undergoes parsing (syntax checking), resolution (checking the existence of referenced objects and columns, permissions checking), and optimization as a unit. • Don't confuse transactions and batches. A transaction is an atomic unit of work. A batch can have multiple transactions, and a transaction can be submitted in parts as multiple batches. When a transaction is cancelled or rolled back in midstream, SQL Server undoes the partial activity that has taken place since the beginning of the transaction, regardless of where the batch began. • Client APIs such as ADO.NET provide you with methods for submitting a batch of code to SQL Server for execution. SQL Server utilities such as SQL Server Management Studio, SQLCMD, and OSQL provide a client command called GO that signals the end of a batch. Note that the GO command is a client command and not a T-SQL server command.

5. A Batch As a Unit of Parsing • A batch is a set of commands that are parsed and executed as a unit. If the parsing is successful, SQL Server will then attempt to execute the batch. In the event of a syntax error in the batch, the whole batch is not submitted to SQL Server for execution. For example, the following code has three batches, the second of which has a syntax error (FOM instead of FROM in the second query): • -- Valid batch PRINT 'First batch'; USE TSQLFundamentals2008; GO • -- Invalid batch P PRINT 'Second batch'; SELECT custid FROM Sales.Customers; SELECT orderid FOM Sales.Orders; GO • -- Valid batch PRINT 'Third batch'; SELECT empid FROM HR.Employees;

6. Batches and Variables • Variables are local to the batch in which they are defined. If you try to refer to a variable that was defined in another batch, you get an error saying that the variable was not defined. For example, the following code declares a variable and prints its content in one batch, and then tries to print its content from another batch: DECLARE @i AS INT; SET @i = 10; -- Succeeds PRINT @i; GO -- Fails PRINT @i; • The reference to the variable in the first PRINT statement is valid because it appears in the same batch where the variable was declared, but the second reference is invalid. Therefore the first PRINT statement returns the variable's value (10), while the second fails. Here's the output returned from this code: • 10 Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@i".

7. Statements That Cannot Be Combined in the Same Batch • The following statements cannot be combined with other statements in the same batch: CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW. For example, the following code has an IF statement followed by a CREATE VIEW statement in the same batch and therefore is invalid: • IF OBJECT_ID('Sales.MyView', 'V') IS NOT NULL DROP VIEW Sales.MyView; CREATE VIEW Sales.MyView AS SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders FROM Sales.Orders GROUP BY YEAR(orderdate); GO • An attempt to run this code generates the following error: Msg 111, Level 15, State 1, Line 3 'CREATE VIEW' must be the first statement in a query batch. • To get around the problem, separate the IF and CREATE VIEW statements into different batches by adding a GO command after the IF statement.

8. A Batch As a Unit of Resolution • A batch is a unit of resolution. This means that checking the existence of objects and columns happens at the batch level. Keep this fact in mind when designing batch boundaries. • When you apply schema changes to an object and try to manipulate the object data in the same batch, SQL Server may not be aware of the schema changes yet and fail the data manipulation statement with a resolution error.

9. The GO n Option • The GO command was enhanced in the client tools in SQL Server 2005 to support an argument indicating how many times you want to execute the batch. You use the new option when you want the batch repeated. To demonstrate the enhanced GO command, first create the table T1 in tempdb with an identity column: • IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1; CREATE TABLE dbo.T1(col1 INT IDENTITY); • Next, run the following code to suppress the default output produced by DML statements indicating how many rows were affected: • SET NOCOUNT ON; • Finally, run the following code to define a batch with an INSERT DEFAULT VALUES statement and to execute the batch 100 times: • INSERT INTO dbo.T1 DEFAULT VALUES; GO 100 • Keep in mind that GO is a client command and not a server T-SQL command. This means that regardless of the version of the database engine you are connected to, the GO n command is supported as long as the client tool you are using is SQL Server 2005 or later.

10. Flow Elements • Flow elements allow you to control the flow of your code. T-SQL provides very basic forms of control of flow elements including the IF … ELSE element and the WHILE element • The IF … ELSE Flow Element • WHILE

11. IF ELSE • The IF … ELSE element allows you to control the flow of your code based on a predicate. You specify a statement or statement block that is executed if the predicate is TRUE, and optionally a statement or statement block that is executed if the predicate is FALSE or UNKNOWN. • For example, the following code checks whether today is the last day of the year (today's year is different than tomorrow's year). If true, the code prints a message saying that today is the last day of the year; if else, the code prints a message saying that today is not the last day of the year: • IF YEAR(CURRENT_TIMESTAMP) <> YEAR(DATEADD(day, 1, CURRENT_TIMESTAMP)) PRINT 'Today is the last day of the year.' ELSE PRINT 'Today is not the last day of the year.' • In this example I use PRINT statements to demonstrate which parts of the code were executed and which weren't, but of course you can specify other statements as well. • Keep in mind that T-SQL uses three-valued logic and that the ELSE block is activated when the predicate is either FALSE or UNKNOWN. In cases where both FALSE and UNKNOWN are possible outcomes of the predicate (for example, when NULLs are involved), and you need different treatment for each case, make sure you have an explicit test for NULLs with the IS NULL predicate.

12. WHILE • T-SQL provides the WHILE element to enable you to execute code in a loop. The WHILE element executes a statement or statement block repeatedly while the predicate you specify after the WHILE keyword is TRUE. When the predicate is FALSE or UNKNOWN, the loop terminates. • T-SQL doesn't provide a built-in looping element that executes a predetermined number of times, but it's very easy to mimic such an element with a WHILE loop and a variable. For example, the following code demonstrates how to write a loop that iterates 10 times: • DECLARE @i AS INT; SET @i = 1 WHILE @i <= 10 BEGIN PRINT @i; SET @i = @i + 1; END; • The code declares an integer variable called @i that serves as the loop counter and initializes it with the value 1. The code then enters a loop that iterates while the variable is smaller than or equal to 10. In each iteration the code in the loop's body prints the current value of @i and then increments it by 1.

13. An Example of Using IF and WHILE • The following example combines the use of the IF and WHILE elements. The purpose of the code in this example is to create a table called dbo.Nums in the tempdb database and populate it with 1,000 rows with the values 1 through 1000 in the column n: • SET NOCOUNT ON; USE tempdb; IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums; CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY); GO DECLARE @i AS INT; SET @i = 1; WHILE @i <= 1000 BEGIN INSERT INTO dbo.Nums(n) VALUES(@i); SET @i = @i + 1; END • The code uses the IF statement to check whether the table Nums already exists in tempdb, and if it does, the code drops it. The code then uses a WHILE loop to iterate 1,000 times and populate the Nums table with the values 1 through 1000

14. Cursors • T-SQL also supports an object called cursor that allows you to process rows from a result set of a query one at a time and in requested order. This is in contrast to using set-based queries—normal queries without a cursor where you manipulate the set or multiset as a whole, and cannot rely on order.

15. Cursors • You have a compelling reason to do otherwise should you consider using cursors. This recommendation is based on several factors: • First and foremost, when you use cursors you pretty much go against the relational model that wants you to think in terms of sets. • Second, the record-by-record manipulation done by the cursor has overhead. A certain extra cost is associated with each record manipulation by the cursor compared to set-based manipulation. Given a set-based query and cursor code that do similar physical processing behind the scenes, the cursor code is usually several dozens of time slower than the set-based code. • Third, with cursors you spend a lot of code on the physical aspects of the solution, or in other words, on how to process the data (declaring the cursor, opening it, looping through the cursor records, closing the cursor, deallocating the cursor). With set-based solutions you mainly focus on the logical aspects of the solution—in other words, on what to get instead of on how to get it. Therefore, cursor solutions tend to be longer, less readable, and harder to maintain compared to set-based solutions.

16. Cursors • Working with a cursor generally involves the following steps: • Declare the cursor based on a query. • Open the cursor. • Fetch attribute values from the first cursor record into variables. • While the end of the cursor is not reached (the value of a function called @@FETCH_STATUS is 0), loop through the cursor records; in each iteration of the loop fetch attribute values from the current cursor record into variables, and perform the processing needed for the current row. • Close the cursor. • Deallocate the cursor.

17. Temporary Tables • Local Temporary Tables • You create a local temporary table by naming it with a single number sign as a prefix, such as #T1. All three kinds of temporary tables are created in the tempdb database. • A local temporary table is visible only to the session that created it, in the creating level and all inner levels in the call stack (inner procedures, functions, triggers, and dynamic batches). A local temporary table is destroyed automatically by SQL Server when the creating level in the call stack gets out of scope. • For example, suppose that a stored procedure called Proc1 calls a procedure called Proc2, which in turn calls a procedure called Proc3, which in turn calls a procedure called Proc4. Proc2 creates a temporary table called #T1 before calling Proc3. The table #T1 is visible to Proc2, Proc3, and Proc4 but not to Proc1, and is destroyed automatically by SQL Server when Proc2 finishes. • If the temporary table is created in an ad-hoc batch in the outermost nesting level of the session (the value of the @@NESTLEVEL function is 0), it is visible to all subsequent batches as well, and is destroyed by SQL Server automatically only when the creating session disconnects.

18. Temporary Tables • Global Temporary Tables • When you create a global temporary table, it is visible to all other sessions. They are destroyed automatically by SQL Server when the creating session disconnects, and there are no active references to the table. You create a global temporary table by naming it with two number signs as a prefix, such as ##T1. • Global temporary tables are useful when you want to share temporary data with everyone. No special permissions are required and everyone has full DDL and DML access. Of course, the fact that everyone has full access means that anyone can even drop the table, so consider the alternatives carefully. • For example, the following code creates a global temporary table called ##Globals with columns called id and val: • CREATE TABLE dbo.##Globals ( id sysname NOT NULL PRIMARY KEY, val SQL_VARIANT NOT NULL ); • This table is supposed to mimic global variables that are not supported by SQL Server. The id column is of a sysname data type—the type SQL Server uses internally to represent identifiers, and the val column is of a SQL_VARIANT data type—a generic type that can store within it a value of almost any base type.

19. Tables Variables • Table variables are similar to local temporary tables in some ways and different in others. You declare table variables similarly to the way you declare other variables, by using the DECLARE statement. • As with local temporary tables, table variables have a physical presence as a table in the tempdb database, contrary to the common misconception that they exist only in memory. • Like local temporary tables, table variables are visible only to the creating session, but have a more limited scope, which is only the current batch. Table variables are visible neither to inner batches in the call stack nor to subsequent batches in the session. • If an explicit transaction is rolled back, changes made to temporary tables in that transaction are rolled back as well; however, changes made to table variables by statements that completed in the transaction aren't rolled back. • Only changes made by the active statement that failed or was terminated before completion are undone.

20. Table Types • SQL Server 2008 introduces support for table types. By creating a table type you preserve a table definition in the database and can later reuse it as the table definition of table variables and input parameters of stored procedures and user-defined functions. • For example, the following code creates a table type called dbo.OrderTotalsByYear in the TSQLFundamentals2008 database: • USE TSQLFundamentals2008; IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL DROP TYPE dbo.OrderTotalsByYear; CREATE TYPE dbo.OrderTotalsByYear AS TABLE ( orderyear INT NOT NULL PRIMARY KEY, qty INT NOT NULL ); • Once the table type is created, whenever you need to declare a table variable based on the table type's definition, you won't need to repeat the code