As your systems become more complex, you'll need to spend more time carefully integrating SQL code with your host application code. In this chapter, you review the logic and flow control statements that you have available to you in your SQL code.
At a high-level, Stored Procedures are a way that you can create routines and procedures that are run on the server, by server processes. These routines can be started by an application calling them, or called by data integrity rules or triggers.
The benefit of Stored Procedures comes from the fact that they run within the SQL Server environment on the server. Although at first this might not seem to be any obvious advantage, it goes to the heart of the client-server model. Remember the rule of thumb that the system doing the work to satisfy a given situation should be the system most suited for that work? Since SQL Server manages the databases in your system, it makes sense that it would be the best place to run the stored procedures against that data. Stored procedures can return values, modify values, and can be used to compare a user- supplied value against the pre-requisites for information in the system. They run quickly, with the added horsepower of the average SQL Server hardware, and they are database-aware and able to take advantage of SQL Server's optimizer for best performance at runtime.
You can also pass values to a stored procedure, and it can return values that are not necessarily part of an underlying table, but are, instead, calculated during the running of the stored procedure.
The benefits of stored procedures on a grand scale include:
NOTE: It's important to keep the client/server model in mind when you're building your systems. Remember, data management belongs on the server, and data presentation and display manipulation for reports and inquiries should reside on the client in the ideal model. As you build systems, be on the lookout for those items that can be moved to the different ends of the model to optimize the user's experience with your application.
Although SQL is defined as a non-procedural language, SQL Server permits the use of flow-control keywords. You use the flow-control keywords to create a procedure that you can store for subsequent execution. You can use these stored procedures to perform operations with an SQL Server database and its tables instead of writing programs using conventional programming language, such as C or Visual Basic.
Some of the advantages that Stored Procedures offer over dynamic SQL Statements are:
You use the CREATE PROC[EDURE] statement to create a stored procedure. Permission to execute the procedure that you create is set by default to the owner of the database. An owner of the database can change the permissions to allow other users to execute the procedure. The maximum stored procedure name length is 30 characters. The syntax that you use to define a new procedure is as follows:
CREATE PROCEDURE [owner,] procedure_name [;number] [@parameter_name datatype [=default] [OUTput] ... [@parameter_name datatype [=default] [OUTput] [FOR REPLICATION] | [WITH RECOMPILE] , ENCRYPTION AS sql_statements
CAUTION: Be sure you reload your stored procedures again after information has been saved in the database tables that represents, both in volume and content, the information that your application can expect to see. Because stored procedures are compiled and optimized based on the tables, indexes, and data loading, your query can show significant improvement just by reloading it after "real" information has been placed in the system.
In Listing 14.1, a simple procedure is created that contains a SELECT statement to display all rows of a table. After the procedure is created, its name is entered on a line to execute the procedure. If you precede the name of a stored procedure with other statements, you use the EXEC[UTE] procedure name statement to execute the procedure.
create procedure all_employees as select * from employees exec all_employees name department badge -------------------- -------------------- ----------- Bob Smith Sales 1234 Mary Jones Sales 5514 ( 2 row(s) affected)
TIP: If your call to the stored procedure is the first in your batch of commands, you don't have to specify the EXEC[UTE] portion of the statement. You can simply call the procedure by name, and it will be executed automatically.
NOTE: As mentioned earlier, naming conventions for SQL objects are an important part of your implementation plan. In a production system, you will often have hundreds of stored procedures, many tables, and many more supporting objects. You should consider coming up with a naming convention for your stored procedures that will make it easy to identify them as procedures and will make it easier to document them. In many installations, a common prefix for the stored procedure name is sp_.
You can create a new procedure in the current database only. If you're working in ISQL or ISQL/W, you can execute the USE statement followed by the name of the database to set the current database to the database in which the procedure should be created. You can use any Transact-SQL statement in a stored procedure with the exception of CREATE statements.
When you submit a stored procedure to the system, SQL Server compiles and verifies the routines within it. If any problems are found, the procedure is rejected and you'll need to determine what the problem is prior to re-submitting the routine. If your stored procedure references another, as yet unimplemented stored procedure, you'll receive a warning message, but the routine will still be installed.
If you leave the system with the stored procedure that you previously referred to uninstalled, the user will receive an error message at runtime.
NOTE: Stored procedures are treated like all other objects in the database. They are, therefore, subject to all of the same naming conventions and other limitations. For example, the name of a stored procedure cannot contain spaces, and it can be accessed using the database <object> convention.
Stored procedures are very powerful, but to be most effective, the procedure must be somewhat dynamic, which enables you, the developer, to pass in values to be considered during the functioning of the stored procedure. Here are some general guidelines for using parameters with stored procedures:
You can use parameters to pass information into a procedure from the line that executes the parameter. You place the parameters after the name of the procedure on a command line, with commas to separate the list of parameters if there is more than one. You use system datatypes to define the type of information to be expected as a parameter.
In Listing 14.2, the procedure is defined with three input parameters. The defined input parameters appear within the procedure in the position of values in the VALUE clause of an INSERT statement. When the procedure is executed, three literal values are passed into the INSERT statement within the procedure as a parameter list. A SELECT statement is executed after the stored procedure is executed to verify that a new row was added through the procedure.
NOTE: When a procedure executes as the first statement in a series of statements, the procedure does not have to be preceded by the keyword EXECUTE. The name of the procedure to be executed is simply placed as the first keyword on the line.
TIP: Be sure to check the documentation for the host language you are using with SQL Server to determine the correct calling sequence for the host language. Actual calling syntax varies from language to language.
create procedure proc4 (@p1 char(15), @p2 char(20), @p3 int) as insert into Workers values (@p1, @p2, @p3) proc4 `Bob Lint',Sales,3333 select * from Workers where Badge=3333 Name Department Badge ------------------------------ --------------- ----------- Bob Lint Sales 3333 (1 row(s) affected)
The semicolon and integer after the name of a procedure enables you to create multiple versions of a procedure with the same name. In Listing 14.3, two procedures with the same name are created as versions one and two. When the procedure is executed, the version number can be specified to control the version of the procedure that is executed. If no version number is specified, the first version of the procedure is executed. This option is not shown in the example above, but is available if needed by your application. Both procedures use a PRINT statement to return a message that identifies the procedure version.
create procedure proc3;1 as print `version 1' create procedure proc3;2 as print `version 2' proc3;1 version 1 proc3;2 version 2 proc3 version 1
In the previous example, proc3 is executed without preceding it with the keyword EXECUTE because it is executed as the first statement on a line.
TIP: You can use the SET NOEXEC ON command the first time that you execute a procedure to check it for errors. This prevents you from executing it when errors may cause it to fail.
You can create a new stored procedure through the SQL Enterprise Manager, as well as in ISQL or ISQL/W. Perform the following steps to create a new stored procedure through the SQL Enterprise Manager:
FIG. 14.1
You can also edit an existing stored procedure in the Manage Stored Procedures
dialog box.
FIG. 14.2
Click the Procedures list box to display a list of the procedures in the selected
database.
When you call stored procedures from other applications environments, there are a few useful tricks that you should know about. For starters, when your stored procedures take parameters, you have a couple of different options.
First, you can always provide all parameters in the order in which they are declared. Although this is easy to develop for, consider carefully whether this makes sense in the long run. There will probably be cases where you want to make a multipurpose stored procedure that calls for more parameters than would be required, on the whole, for any given call. In those cases, you're expecting to have some parameters that are not specified in each call.
You use a test for null on a parameter to determine whether it was provided. This means you can test directly against Null, or you can use the IsNull comparison operator.
See Chapter 9, "Using Functions," in the section titled "Using ISNULL and NULLIF," for more information about ISNULL.
On the application side, it can be quite cumbersome to have to specify each value on every call to the stored procedure, even in cases where the value is NULL. In those cases, the calling application can use named arguments to pass information to SQL Server and the stored procedure. For example, if your stored procedure allows up to three different arguments, name, address, and phone, you can call the routine as follows:
Exec sp_routine @name="blah"
When you provide the name of the argument being passed, SQL Server can map it to its corresponding parameter. This is, typically, the best way to pass information to SQL Server, and it also helps make the code more readable because you can tell which parameters are being passed.
You use the system procedure sp_helptext to list the definition of a procedure, and sp_help to display control information about a procedure. The system procedures sp_helptext and sp_help are used to list information about other database objects, such as tables, rules, and defaults, as well as stored procedures.
Procedures with the same name, regardless of version number, are displayed together and dropped together. In Listing 14.4, the definition of procedures proc3, version one and two, are both displayed when the procedure is specified with the sp_helptext system procedure.
sp_helptext proc3 text --------------------------------------------- create procedure proc3;1 as print `version 1' create procedure proc3;2 as print `version 2' (1 row(s) affected)
In Listing 14.5, the system procedure sp_help is used to display information about the procedure proc3. If the version number is used with the sp_help system procedure, an error is returned.
sp_help proc3 Name Owner Type ------------------------------ ------------------------------ ---------------- proc3 dbo stored procedure Data_located_on_segment When_created ----------------------- --------------------------- not applicable Dec 7 1994 1:50PM
You can use an additional system procedure just to return information about stored procedures. The system procedure sp_stored_procedures is used to list information about stored procedures. In the following example, the procedure sp_stored_procedures is used to display information about a previously stored procedure.
sp_stored_procedures procall procedure_qualifier procedure_owner procedure_name num_input_params num_output_params num_result_sets remarks ---------------------------- master dbo procall;1 -1 -1 -1 (null) (1 row(s) affected)
TIP: You can use the command SET SHOWPLAN ON before you execute a procedure to see how SQL Server will perform the necessary reads and writes to the database tables when the statements in your procedure are executed. You can use this information to help determine whether additional indexes or different data layout would be beneficial to the query.
You use the SQL Enterprise Manager to list and edit existing procedures. Double-click the procedure to be edited in the list of stored procedures in the main window of the server Manager. The selected procedure is displayed and can be changed in the Manage Stored Procedures dialog box that is displayed.
You can't directly edit stored procedures. You'll notice in the Manage Stored Procedures dialog box (refer to Figures 14.1 and 14.2) an existing procedures has additional Transact-SQL statements added prior to the procedure definition. The conditional IF statement is used to check whether the procedure is already defined and delete the procedure. The old procedure definition must be removed and a new procedure, with the name you specify, is substituted for the old procedure.
Two closely related tasks that you'll, no doubt, have to perform are making changes to existing stored procedures and removing no longer used stored procedures. In the next two sections, you see exactly how you accomplish both of these tasks and understand why they are so closely related.
Changing an Existing Stored Procedure Stored procedures cannot be modified in place, so you're forced to first drop the procedure, then create it again. Unfortunately, there is no ALTER statement that can be used to modify the contents of an existing procedure. This stems largely from the query plan that is created and the from fact that stored procedures are compiled after they are initiated. Because the routines are compiled, and the query plan relies on the compiled information, SQL Server uses a binary version of the stored procedure when it is executed. It would be difficult or impossible to convert from the binary representation of the stored procedure back to English to allow for edits. For this reason, it's imperative that you maintain a copy of your stored procedures in a location other than SQL Server. Although SQL Server can produce the code that was used to create the stored procedure, you should always maintain a backup copy.
You can pull the text associated with a stored procedure by using the sp_helptext system stored procedure. The syntax of sp_helptext is as follows:
Sp_helptext procedure name
For example, pulling the text associated with the all_employees stored procedure results in the display shown in Figure 14.3.
FIG. 14.3
You can review the text associated with a stored procedure with the sp_helptext
statement.
Alternatively, if you want to review the stored procedure in the Enterprise Manager, you can do so by selecting the Database, Objects, Stored procedures and then double-clicking the stored procedure you want to view. The result, shown in Figure 14.4, is not only a listing of the stored procedure, but also the proper statement to drop it and insert a new copy, should you so desire.
After you have the text, you can recreate the routine with the changes you need.
FIG. 14.4
The Enterprise Manager sets up the syntax to drop and then recreate the stored
procedure, if needed.
Removing Existing Stored Procedures You use the DROP PROCEDURE statement to drop a stored procedure that you've created. Multiple procedures can be dropped with a single DROP PROCEDURE statement by listing multiple procedures separated by commas after the keywords DROP PROCEDURE in the syntax:
DROP PROCEDURE procedure_name_1, ...,procedure_name_n
Multiple versions of a procedure can't be selectively dropped. All versions of a procedure with the same name must be dropped at the same time by using the DROP PROCEDURE statement that specifies the procedure without a version number.
In the following example, the two versions of the procedures proc3 are dropped:
drop procedure proc3 This command did not return data, and it did not return any rows
You can also drop a selected procedure in the SQL Enterprise Manager. Right-click the selected procedure, and choose Drop from the menu that is displayed.
The benefit of using a stored procedure for the execution of a set of Transact-SQL statements is that it is compiled the first time that it's run. During compilation, the Transact-SQL statements in the procedure are converted from their original character representation into an executable form. During compilation, any objects that are referenced in procedures are also converted to alternate representations. For example, table names are converted to their object IDs and column names to their column IDs.
An execution plan is also created just as it would be for the execution of even a single Transact-SQL statement. The execution plan contains, for example, the indexes to be used to retrieve rows from tables that are referenced by the procedure. The execution plan is kept in a cache and is used to perform the queries of the procedure each time it's subsequently executed.
TIP: You can define the size of the procedure cache so that it is large enough to contain most or all the available procedures for execution and save the time that it would take to regenerate the execution plan for procedures.
Normally, the procedure's execution plan is run from the memory cache of procedures that permits it to execute rapidly. A procedure, however, is automatically recompiled under the following circumstances:
NOTE: It's often the case that SQL Server remains up and running on the server system continuously. As a database server, it must be available whenever users on client PC workstations must access the SQL Server databases. The server computer and SQL Server need never be stopped and restarted unless a major error occurs, hardware malfunctions, or an update to a new version of SQL Server or Windows NT is in process. The recompilation of stored procedures would not be done frequently on systems that run non-stop.
TIP: When SQL Server is installed, you can specify that it should automatically restart when the server system is rebooted.
Note that because SQL server attempts to optimize stored procedures by caching the most recently used routines, it is still possible that an older execution plan, one previously loaded in cache, may be used in place of the new execution plan.
To prevent this problem, you must both drop and recreate the procedure or stop and restart SQL Server to flush the procedure cache and ensure that the new procedure is the only one that will be used when the procedure is executed.
You can also create the procedure using a WITH RECOMPILE option so that the procedure is automatically recompiled each time that it's executed. You should do this if the tables accessed by the queries in a procedure are very dynamic. Tables that are very dynamic have rows added, deleted, and updated frequently, which results in frequent changes to the indexes that are defined for the tables.
In other cases, you may want to force a recompilation of a procedure when it would not be done automatically. For example, if the statistics used to determine whether an index should be used for a query are updated, or an entire index is created for a table, recompilation is not automatic. You can use the WITH RECOMPILE clause on the EXECUTE statement when you execute the procedure to do a recompilation. The syntax of the EXECUTE statement with a recompile clause is:
EXECUTE procedure_name AS .Transact-SQL statement(s) ... WITH RECOMPILE
If the procedure you're working with uses parameters and these parameters control the functionality of the routine, you may want to use the RECOMPILE option. This is because, within the stored procedure, if the routine's parameters may determine the best execution path, it may be beneficial to have the execution plan determined at runtime, rather than determining it once and then using this plan for all accesses to the stored procedure.
NOTE: It may be difficult to determine whether a procedure should be created with the WITH RECOMPILE option. If in doubt, you'll probably be better served by not creating the procedure with the RECOMPILE option. This is because, if you create a procedure with the RECOMPILE option, the procedure is recompiled each time the procedure is executed, and you may waste valuable CPU time to perform these compiles. You can still add the WITH RECOMPILE clause to force a recompilation when you execute the procedure.
You can't use the WITH RECOMPILE option in a CREATE PROCEDURE statement that contains the FOR REPLICATION option. You use the FOR REPLICATION option to create a procedure that is executed during replication.
See the Chapter 17 section titled "Setting Up and Managing Replication," for more information about replication.
You can add the ENCRYPTION option to a CREATE PROCEDURE statement to encrypt the defi- nition of the stored procedure that is added to the system table syscomments. You use the ENCRYPTION option to prevent other users from displaying the definition of your procedure and learning what objects it references and what Transact-SQL statements it contains.
CAUTION: Unless you absolutely must encrypt procedures for security reasons, you should leave procedures unencrypted. When you upgrade your database for a version change or to rebuild it, your procedures can only be recreated if the entries in syscomments are not encrypted.
You can use the system stored procedure, sp_makestartup, to define a procedure to execute automatically at the startup of SQL Server. You can mark any number of procedures to execute automatically at startup. The syntax sp_makestartup is as follows:
sp_makestartup procedure_name
The procedures that are defined to execute automatically at startup execute after the last database has been automatically started and recovered at startup of SQL Server. You can use the system procedure sp_helpstartup to list the procedures that are defined to execute at startup. You use the system procedure, sp_unmakestartup, to prevent a procedure from executing automatically.
In Listing 14.5, a new procedure is created that is marked for the automatic execution when SQL Server is started. In addition, the list startup procedures are also listed before and after the procedures are removed from automatic execution at startup.
create procedure test_startup as print `test procedure executed at startup' go sp_makestartup test_startup go Procedure has been marked as `startup'. sp_helpstartup go Startup stored procedures: ------------------------------ test_startup (1 row(s) affected) sp_unmakestartup test_startup go Procedure is no longer marked as `startup'. sp_helpstartup Startup stored procedures:
Sets of Transact-SQL statements are referred to as batches and include stored procedures. The rules or syntax for the use of Transact-SQL statements in batch apply to the following list of objects:
The syntax is primarily a set of restrictions that limit the types of statements that can be used in batch. Most of the restrictions are the statements that create objects, change the database, or query environment, and don't take effect within the current batch.
For example, although rules and defaults can be defined and bound to a column or user- defined datatype within a batch, the defaults and rules are in effect until after the completion of the batch. You can't drop an object and reference or recreate it in the same batch.
Some additional Set options that are defined with a batch don't apply to queries contained in the batch. For example, the Set option SET NOCOUNT ON will affect all queries that follow it within a stored procedure, and suppress the count line for the execution of SELECT statements. The SET SHOWPLAN ON option does not affect the queries used within a stored procedure, and a query plan isn't displayed for the queries in the procedure.
As you've seen throughout this book, if you use the command line ISQL for the execution of a set of Transact-SQL statements, the GO command is used to specify the end of the set of statements. GO is used on a line by itself. The GO command is required if you interactively use a set of statements or read in statements from an input file to ISQL.
The GO command is not required to execute a set of Transact-SQL statements that are used in the Windows GUI application form of ISQL, ISQL/W. GO is also not required in a series of Transact-SQL statements that are executed within batch objects, such as stored procedures, rules, defaults, triggers, or views. In Listing 14.6, the GO command is used first to cause the execution of the USE command and then to signal the end of the second batch, which contains two SELECT statements.
C:>isql/U sa Password: 1>use employees 2>go 1>select * from Workers 2>select count(*) from Workers 3>go Name Department Badge ------------------------------ --------------- ----------- Bob Smith Sales 1234 Sue Simmons Sales 3241 Mary Watkins Field Service 6532 Linda Lovely Library 7888 (4 row(s) affected) ---------- ---------- 4 (1 row(s) affected)
In the following example, the GO command is used with the file query1.sql, which contains the following commands:
use employees go select * from Workers select max(Rownum) from Rownumber go
The Transact-SQL statements within the file are executed with the invocation of ISQL, which returns the display shown in Listing 14.7:
isql /U /i query1.sql /n /P `' Name Department Badge ---------------------------------- ------------------------- ------------ Bob Smith Sales 1234 Sue Simmons Sales 3241 Mary Watkins Field Service 6532 Linda Lovely Library 7888 (4 rows affected) ------------- 19 (1 row affected)
TIP: You can also use a /o file-spec to direct the output of the execution of ISQL to a file instead of to your monitor and to capture the output of any statements executed during the ISQL session.
Transact-SQL contains several statements that are used to change the order of execution of statements within a set of statements, such as a stored procedure. The use of such flow-control statements permits you to organize statements in stored procedures to provide the capabilities of a conventional programming language, such a C or COBOL. You may find that some of the retrieval, update, deletion, addition, and manipulation of the rows of database tables can more easily be performed through the use of flow-control statements in objects, such as stored procedures.
You can use the keywords IF and ELSE to control conditional execution within a batch, such as a stored procedure. The IF and ELSE keywords enable you to test a condition and execute either the statements that are part of the IF branch or the statements that are part of the ELSE branch. You define the condition for testing as an expression following the keyword IF. The syntax of an IF...ELSE statement is as follows:
IF expression statement [ELSE] [IF expression] statement]
NOTE: It's impossible to show examples of the use of conditional statements that can be formed with the keywords IF and ELSE without using other keywords. The examples shown next use the keywords PRINT and EXISTS. In the subsequent examples, the keyword PRINT is used to display a string of characters.
The keyword EXISTS is usually followed by a statement within parentheses when used in an IF statement. The EXISTS statement is evaluated to either True or False, depending on whether the statement within the parentheses returns one or more rows, or no rows, respectively.
You needn't use an ELSE clause as part of an IF statement. The simplest form of an IF statement is constructed without an ELSE clause. In the following example, a PRINT statement is used to display a confirmation message that a row exists in a database table. If the row doesn't exist in the table, the message, No entry, is displayed. Unfortunately, the message is also displayed after the verification message is displayed because you're not using the ELSE option.
if exists (select * from Workers where Badge=1234) print `entry available' print `No entry' entry available No entry
In the following example, the row isn't found in the table so only the PRINT statement that follows the IF statement is executed:
if exists (select * from Workers where Badge=1235) print `entry available' print `No entry' No entry
The previous two examples show the problem of using an IF statement that doesn't contain an ELSE clause. In the examples, it's impossible to prevent the message, No entry, from appearing. You add an ELSE clause to the IF statement to print the No entry message, if a row isn't found and the condition after the IF isn't True.
In the following example, our previous examples are rewritten to use IF and ELSE clauses. If a row that is tested for in the IF clause is in the table, only the message, employee present, is displayed. If the row isn't found in the table, only the message, employee not found, is displayed:
if exists (select * from employees where name='Bob Smith') print `employee present' else print `employee not found'
CAUTION: Unlike some programming languages you may have used, when used alone, the Transact-SQL IF statement can have only one statement associated with it. As a result, there is no need for a keyword, such as END-IF, to define the end of the IF statement. See Using BEGIN...END in the next section for information on grouping statements and associating them with an IF...ELSE condition.
You use the keywords BEGIN and END to designate a set of Transact-SQL statements to be executed as a unit. You use the keyword BEGIN to define the start of a block of Transact-SQL statements. You use the keyword END after the last Transact-SQL statement that is part of the same block of statements. BEGIN...END uses the following syntax:
BEGIN statements END
You often use BEGIN and END with a conditional statement such as an IF statement. BEGIN and END are used in an IF or ELSE clause to permit multiple Transact-SQL statements to be executed if the expression following the IF or ELSE clause is True. As mentioned earlier, without a BEGIN and END block enclosing multiple statements, only a single Transact-SQL statement can be executed if the expression in the IF or ELSE clause is True.
In Listing 14.8, BEGIN and END are used with an IF statement to define the execution of multiple statements if the condition tested is True. The IF statement contains only an IF clause; no ELSE clause is part of the statement.
if exists (select * from employees where badge=1234) begin print `entry available' select name,department from employees where badge=1234 end entry available name department -------------------- -------------------- Bob Smith Sales (1 row(s) affected)
In Listing 14.9, an ELSE clause is added to the IF statement to display a message if the row isn't found:
if exists (select * from employees where department='Sales') begin print `row(s) found' select name, department from employees where department='Sales' end else print `No entry' row(s) found name department -------------------- -------------------- Bob Smith Sales Mary Jones Sales (2 row(s) affected)
Listing 14.10 returns the message that follows the ELSE clause because no row is found:
if exists (select * from employees where department='Nonexistent') begin print `row(s) found' select name, department from employees where department='Nonexistent' end else print `No entry' No entry
You use the keyword WHILE to define a condition that executes one or more Transact-SQL statements when the condition tested evaluates to True. The statement that follows the expression of the WHILE statement continues to execute as long as the condition tested is True. The syntax of the WHILE statement is as follows:
WHILE <boolean_expression> <sql_statement>
NOTE: As with the IF...ELSE statements, you can only execute a single SQL statement with the WHILE clause. If you need to include more than one statement in the routine, use the BEGIN...END construct as described above.
In Listing 14.11, a WHILE statement is used to execute a SELECT statement that displays a numeric value until the value reaches a limit of five. The example uses a variable that is like a parameter in that a variable is a named storage location. You define the datatype of a variable using a DECLARE statement to control the way information is represented in the variable. A variable is always referenced preceded by an at sign (@) like a parameter.
In the example, the value stored in the variable is initialized to one and subsequently incre- mented. The statements associated with the WHILE execute until the variable x reaches a value of five:
declare @x int select @x=1 while @x<5 begin print `x still less than 5' select @x=@x+1 end go (1 row(s) affected) x still less than 5 (1 row(s) affected) x still less than 5 (1 row(s) affected) x still less than 5 (1 row(s) affected) x still less than 5 (1 row(s) affected)
A more meaningful example of the use of a WHILE statement can be shown after two additional Transact-SQL keywords are introduced and explained. An example using WHILE along with the keywords BREAK and CONTINUE will be shown a little later in this section.
You use the keyword BREAK within a block of Transact-SQL statements that is within a conditional WHILE statement to end the execution of the statements. The execution of a BREAK results in the first statement following the end of block to begin executing. The syntax of a BREAK clause is as follows:
WHILE <boolean_expression> <sql_statement> BREAK <sql_statement>
In Listing 14.12, the BREAK within the WHILE statement causes the statement within the WHILE to terminate. The PRINT statement executes once because the PRINT statement is located before the BREAK. After the BREAK is encountered, the statements in the WHILE clause aren't executed again.
declare @x int select @x=1 while @x<5 begin print `x still less than 5' select @x=@x+1 break end (1 row(s) affected) x still less than 5 (1 row(s) affected)
You use a CONTINUE keyword to form a clause within a conditional statement, such as a WHILE statement, to explicitly continue the set of statements that are contained within the conditional statement. The syntax of the CONTINUE clause is as follows:
WHILE <boolean_expression> <statement> BREAK <statement> CONTINUE
In Listing 14.13, a CONTINUE is used within a WHILE statement to explicitly define that execution of the statements within the WHILE statement should continue as long as the condition specified in the expression that follows WHILE is True. The use of CONTINUE in the following example skips the final PRINT statement:
declare @x int select @x=1 while @x<5 begin print `x still less than 5' select @x=@x+1 continue print `this statement will not execute' end (1 row(s) affected) x still less than 5 (1 row(s) affected) x still less than 5 (1 row(s) affected) x still less than 5 (1 row(s) affected) x still less than 5 (1 row(s) affected)
Although the two previous examples use BREAK and CONTINUE alone, you don't typically use either CONTINUE or BREAK within a WHILE statement alone. Both BREAK and CONTINUE are often used following an IF or ELSE that are defined within a WHILE statement, so an additional condition can be used to break out of the WHILE loop. If two or more loops are nested, BREAK exits to the next outermost loop.
In Listing 14.14, a BREAK is used with an IF statement, both of which are within a WHILE statement. The BREAK is used to terminate the statements associated with the WHILE if the condition specified by the IF statement is True. The IF condition is True if the value of the local variable, @y, is True:
declare @x int declare @y tinyint select @x=1, @y=1 while @x<5 begin print `x still less than 5' select @x=@x+1 select @y=@y+1 if @y=2 begin print `y is 2 so break out of loop' break end end print `out of while loop' (1 row(s) affected) x still less than 5 (1 row(s) affected) (1 row(s) affected) y is 2 so break out of loop out of while loop
In Listing 14.15, a WHILE statement is used to permit only the rows of a table that match the criteria defined within the expression of the WHILE statement to have their values changed:
begin tran while (select avg(price)from titles) < $30 begin select title_id, price from titles where price >$20 update titles set price=price * 2 end (0 row(s) affected) title_id price -------- -------------------------- PC1035 22.95 PS1372 21.59 TC3218 20.95 (3 row(s) affected) (18 row(s) affected) (0 row(s) affected) title_id price -------- -------------------------- BU1032 39.98 BU1111 23.90 BU7832 39.98 MC2222 39.98 PC1035 45.90 PC8888 40.00 PS1372 43.18 PS2091 21.90 PS3333 39.98 TC3218 41.90 TC4203 23.90 TC7777 29.98 (12 row(s) affected) (18 row(s) affected) (0 row(s) affected)
You must be careful when defining the WHILE statement and its associated statements. As shown in the following example, if the condition specified with the WHILE expression continues to be True, the WHILE loop executes indefinitely.
while exists (select hours_worked from pays) print `hours worked is less than 55' (0 row(s) affected) hours worked is less than 55 (0 row(s) affected) ...
If the evaluation of the expression following the WHILE returns multiple values, you should use an EXISTS instead of any comparison operators. In the following example, the error message that is returned is descriptive of the problem:
while (select hours_worked from pays) > 55 print `hours worked is less than 55' Msg 512, Level 16, State 1 Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression. Command has been aborted.
In this case, you'll want to use EXISTS to determine the comparison value.
You may recall that earlier in this chapter variables were described as similar to parameters in that they are named storage locations. Variables in Transact-SQL can be either local or global. You define local variables by using a DECLARE statement and assigning the variable a datatype. You assign an initial value to local variables with a SELECT statement.
You must declare, assign a value, and use a local variable within the same batch or stored procedure. The variable is only available for use within the same batch or procedure, hence the name local variable.
You can use local variables in batch or stored procedures for such things as counters and temporary holding locations for other variables. Recall that local variables are always referenced with an @ preceding their names. You can define the datatype of a local variable as a user-defined datatype, as well as a system datatype. One restriction that applies to local variables is that you can't define a local variable as a text or image datatype.
The syntax of a local variable is as follows:
DECLARE @variable_name datatype [,variable_name datatype...]
The SELECT statement is used to assign values to local variables, as shown in the following syntax:
SELECT @variable_name = expression |select statement [,@variable_name = expression select statement] [FROM list of tables] [WHERE expression] [GROUP BY...] [HAVING ...] [ORDER BY...]
If the SELECT statement returns more than a single value, the variable is assigned to the last value returned. In Listing 14.16, two local variables are defined and used to return the number of rows in the table. The CONVERT function must be used to convert the numeric format of the number of rows to a text datatype for the PRINT statement. The message that is displayed by the PRINT statement is first built and assigned to a local variable because the concatenation can't be done within the PRINT statement:
declare @mynum int select @mynum = count(*)from Workers declare @mychar char(2) select @mychar = convert(char(2),@mynum) declare @mess char(40) select @mess ='There are ` + @mychar + `rows in the table Workers' print @mess (1 row(s) affected) (4 row(s) affected) (1 row(s) affected) There are 4 rows in the table Workers
Each SELECT statement returns a count message in the previous example. If you want the count message suppressed, you must first execute the SET NOCOUNT statement. In Listing 14.17, the same statements that were executed in the previous example are re-executed with the count turned off:
declare @mynum int select @mynum = count(*)from Workers declare @mychar char(2) select @mychar = convert(char(2),@mynum) declare @mess char(40) select @mess ='There are ` + @mychar + `rows in the table Workers' print @mess There are 4 rows in the table Workers
You'll recall that in examples shown earlier in this chapter, PRINT was used to display a message to the assigned output device. You use the keyword PRINT to display ASCII text or variables up to 255 characters in length. You can't use PRINT to output datatypes other than CHAR, VARCHAR, or the global variable @@VERSION.
Recall that you can't concatenate string data in a PRINT statement directly. You must concatenate text or variables into a single variable and output the results with the PRINT statement. The syntax of the PRINT statement is as follows:
PRINT `text' |@local_variable | @@global_variable
Although your stored procedure parameters are limited in scope to the procedure in which they are defined, SQL Server has several intrinsic global variables. These variables, defined and maintained by the system, are available at any time within your stored procedures. Keep the following guidelines in mind when you work with global variables:
You reference a global variable to access server information or information about your operations. Table 14.1 lists the names of all Microsoft SQL Server global variables and a brief description of the information that is contained within them.
Global Variable | Description |
@@CONNECTIONS | total logons or attempted logins |
@@CPU_BUSY | cumulative CPU Server time in ticks |
@@DBTS | value of unique timestamp for database |
@@ERROR | last system error number, 0 if successful |
@@FETCH_STATUS | status of the last FETCH statement |
@@IDENTITY | the last inserted identity value |
@@IDLE | cumulative CPU Server idle time |
@@IO_BUSY | cumulative Server I/O time |
@@LANGID | current language ID |
@@LANGUAGE | current language name |
@@MAX_CONNECTIONS | max simultaneous connections |
@@MAX_PRECISION | precision level for decimal and numeric datatypes |
@@MICROSOFTVERSION | internal version number of SQL Server |
@@NESTLEVEL | current nested level of calling routines from 0 to 16 |
@@PACK_RECEIVED | number of input packets read |
@@PACKET_SENT | number of output packets written |
@@PACKET_ERRORS | number of read and write packet errors |
@@PROCID | current stored procedure ID |
@@ROWCOUNT | number of rows affected by last query |
@@SERVERNAME | name of local server |
@@SERVICENAME | name of the running service |
@@SPID | current process server ID |
@@TEXTSIZE | current of max text or image data with default of 4K |
@@TIMETICKS | number of microseconds per tick-machine independent. tick is 31.25 milliseconds/1/32 sec. |
@@TOTAL_ERRORS | number of errors during reads or writes |
@@TOTAL_READ | number of disk reads (not cache) |
@@TOTAL_WRITE | number of disk writes |
@@TRANCOUNT | current user total active transactions |
@@VERSION | date and version of SQL Server |
In Listing 14.18, a global variable is used to retrieve the version of SQL Server, which is concatenated with a string literal and the contents of a second global variable:
PRINT @@VERSION declare @mess1 char(21) select @mess1 = `Server name is ` + @@servername PRINT @mess1 Microsoft SQL Server 6.50 - 6.50.201 (Intel X86) Apr 3 1996 02:55:53 Copyright 1988-1996 Microsoft Corporation (1 row(s) affected) Server name is Primary
Several additional keywords can be used within stored procedures or batches of Transact-SQL commands. These additional keywords don't fall into a single descriptive category of similar function. Some of these keywords are GOTO, RETURN, RAISERROR, WAITFOR, and CASE.
You use a GOTO to perform a transfer from a statement to another statement that contains a user-defined label. A GOTO statement used alone is unconditional. The statement that contains the destination label name follows rules for identifiers and is followed by a colon (:).
You only use the label name without the colon on the GOTO line. The syntax of the GOTO statement is as follows:
label: GOTO label
Listing 14.19 shows the use of the GOTO statement that transfers control to a statement that displays the word yes until the value of a variable reaches a specified value. The COUNT was turned off prior to execution of the statements in the example:
declare @count smallint select @count =1 restart: print `yes' select @count =@count + 1 while @count <= 4 goto restart yes yes yes yes
You use the RETURN statement to formally exit from a query or procedure and optionally provide a value to the calling routine. A RETURN is often used when one procedure is executed from within another. The RETURN statement, when used alone, is unconditional, though you can use the RETURN within a conditional IF or WHILE statement. The syntax of the RETURN statement is as follows:
RETURN [integer]
You can use a RETURN statement at any point in a batch or procedure. Any statements that follow the RETURN are not executed. A RETURN is similar to a BREAK with one difference. A RETURN, unlike a BREAK, can be used to return an integer value to the procedure that invoked the procedure that contains the RETURN. Execution of statements continue at the statement following the statement that executed the procedure originally.
To understand the use of the RETURN statement, you must first understand the action performed by SQL Server when a procedure completes execution. SQL Server always makes an integer value available when a procedure ends. A value of zero indicates that the procedure executed successfully. Negative values from -1 to -99 indicate reasons for the failure of statements within the procedure. These integer values are always returned at the termination of a procedure even if a RETURN statement isn't present in a procedure.
You can optionally use an integer value that follows the RETURN statement to replace the SQL Server value with your own user-defined value. You should use non-zero integer values so that your return status values don't conflict with the SQL Server status values. If no user-defined return value is provided, the SQL Server value is used. If more than one error occurs, the status with the highest absolute value is returned. You can't return a NULL value with a RETURN statement. Table 14.2 shows several of the return status values that are reserved by SQL Server.
Return Value | Meaning |
0 | successful execution |
-1 | missing object |
-2 | datatype error |
-3 | process was chosen as a deadlock victim |
-4 | permission error |
-5 | syntax error |
-6 | miscellaneous user error |
-7 | resource error, such as out of space |
-8 | nonfatal internal problem |
-9 | system limit was reached |
-10 | fatal internal inconsistency |
-11 | fatal internal inconsistency |
-12 | table or index is corrupt |
-13 | database is corrupt |
-14 | hardware error |
You must provide a local variable that receives the returned status in the EXECUTE statement, which invokes the procedure that returns status. The syntax to specify a local variable for the returned status value is the following:
EXEC[ute] @return_status=procedure_name
Listing 14.20 shows a return value from a called procedure that executes successfully and returns zero (0). The example shows the definition of the called procedure proc1. This stored procedure is executed from a set of Transact-SQL statements entered interactively.
NOTE: When a set of Transact-SQL statements execute together, whether the statements are part of a procedure or not, the rules for batch operations apply. This is true even if the set of statements are typed in interactively.
NOTE: A procedure that is invoked within another procedure with an EXECUTE statement is most often referred to as a called procedure. Call refers to an equivalent operation used in some programming languages. The keyword used in these languages to invoke the equivalent of a section of code from a program is CALL. This is the same as running a subroutine or function in these other languages.
Although the called procedure doesn't contain a RETURN statement, SQL Server returns an integer status value to the procedure that called proc1.
create procedure proc1 as select * from employees declare @status int execute @status = proc1 select status = @status name department badge -------------------- -------------------- ----------- Bob Smith Sales 1234 Mary Jones Sales 5514 (2 row(s) affected) status ----------- 0 (1 row(s) affected)
In Listing 14.21, proc2 is identical to the procedure proc1 that was used in the previous example except that proc2 contains a RETURN statement with a user-defined positive integer value. A SELECT statement is to display the returned status value from proc2 to confirm that the specified value on the RETURN statement in proc2 is returned to the next statement after the statement that executed proc2.
create procedure proc2 as select * from employees return 5 declare @status int execute @status = proc2 select status = @status name department badge -------------------- -------------------- ----------- Bob Smith Sales 1234 Mary Jones Sales 5514 (1 row(s) affected) status ---------- 5 (1 row(s) affected)
In Listing 14.22, the returned value is checked as part of a conditional statement. A message is displayed if the procedure executed successfully. This third example of Transact-SQL return statements is more typical of the usage of return status in a production environment.
declare @status int execute @status = proc1 if (@status = 0) begin print `' print `proc1 executed successfully' end name department badge -------------------- -------------------- ----------- Bob Smith Sales 1234 Mary Jones Sales 5514 proc2 executed successfully
TIP: You can nest procedures within other procedures up to 16 levels in Transact-SQL.
You use the RAISERROR statement to return a user-specified message in the same form that SQL Server returns errors. The RAISERROR also sets a system flag to record that an error has occurred. The syntax of the RAISERROR statement is as follows:
RAISERROR (<integer_expression>|<`text of message'>, [severity] [, state] Â [, argument1] [, argument2] ) [WITH LOG]
The integer_expression is a user-specified error or message number and must be in the range 50,000 to 2,147,483,647. The integer_expression is placed in the global variable, @@ERROR, which stores the last error number returned. An error message can be specified as a string literal or through a local variable. The text of the message can be up to 255 characters and is used to specify a user-specified error message. A local variable that contains an error message can be used in place of the text of the message. RAISERROR always sets a default severity level of 16 for the returned error message.
In Listing 14.23, a local variable is defined as a character datatype that is large enough to receive the error number specified in the RAISERROR statement after the error number is converted from the global variable, @@ERROR. The RAISERROR statement first displays the message level, state number, and the error message, Guru meditation error. The error number 99999 is then displayed separately using a PRINT statement.
declare @err char(5) raiserror 99999 `Guru meditation error' select @err=convert(char(5),@@ERROR) print @err go Msg 99999, Level 16, State 1 Guru meditation error (1 row(s) affected) 99999
You can also add your message text and an associated message number to the system table sysmessages. You use the system stored procedure, sp_addmessage, to add a message with a message identification number within the range 50,001 and 2,147,483,647. The syntax of the sp_addmessage system procedure is as follows:
sp_addmessage message_id, severity, message text' [, language [, {true | false} [, REPLACE]]]
CAUTION: If you enter a user-specified error number that has not been added to the sysmessages table and do not explicitly specify the message text, you'll receive an error that the message can't be located in the system table as shown in the following example:raiserror (99999,7,2)
go
Msg 2758, Level 16, State 1
RAISERROR could not locate entry for error 99999 in Sysmessages.
User-defined error messages generated with a RAISERROR statement, but without a number in the sysmessages table return a message identification number of 50,000.
The severity level is used to indicate the degree or extent of the error condition encountered. Although severity levels can be assigned in the range of one through 25, you should usually assign your system message a severity level value from 11-16.
Severity levels of 11-16 are designed to be assigned through the sp_addmessages statement, and you can't assign a severity level of from 19-25 unless you're logged in as the administrator. Severity levels 17-19 are more severe software or hardware errors, which may not permit your subsequent statements to execute correctly.
Severity levels of 20-25 are severe errors and won't permit subsequent Transact-SQL statements to execute. System messages that have severity levels over nineteen can be problems, such as connection problems between a client system and the database server system or corrupted data in the database.
NOTE: Microsoft suggests that severe errors, those that have a severity level of nineteen or higher, should also notify the database administrator. The database administrator needs to know of these problems because such problems are likely to impact many different users and should be attended to as soon as possible.
When specifying messages, you enter an error message within single quotes of up to 255 characters. The remaining parameters of the sp_addmessage procedure are optional. The language parameter specifies one of the languages SQL Server was installed with. U.S. English is the default language if the parameter is omitted.
The next parameter, either True or False, controls whether the system message is automatically written to the Windows NT application event log. Use True to have the system message written to the event log. In addition, True results in the message being written to the SQL Server error log file.
The last parameter, REPLACE, is used to specify that you want to replace an existing user-defined message in the sysmessages table with a new entry.
Listing 14.24 shows the use of the sp_addmessage system stored procedure that adds a system message with an associated identification number and severity. A subsequent SELECT statement retrieves the message from the system table sysmessages. Finally, the RAISERROR statement is used to return the user-defined system message.
sp_addmessage 99999,13,'Guru meditation error' go select * from sysmessages where error=99999 go raiserror (99999, 13,-1) go New message added. error severity dlevel description languid ----------- -------- ------ ------------------ ------ 99999 13 0 Guru meditation error 0 (1 row(s) affected) Msg 99999, Level 13, State 1 Guru meditation error
You can use the system-stored procedure, sp_dropmessage, to remove a user-defined message from the system table sysmessages when it is no longer needed. The syntax of the sp_dropmessage is as follows:
sp_dropmessage [message_id [, language | `all']]
You're only required to enter the message number to drop the message. The two additional optional parameters permit you to specify the language from which the message should be dropped. You can use the keyword all to drop the user-defined message from all languages.
In the following example, a user-defined message in the default language of U.S. English is removed from the system table, sysmessages:
sp_dropmessage 99999 go Message dropped.
You use a WAITFOR statement to specify a time, a time interval, or an event for executing a statement, statement block, stored procedure, or transaction. The syntax of the WAITFOR statement is as follows:
WAITFOR {DELAY <`time'> | TIME <`time'> | ERROREXIT | PROCESSEXIT | MIRROREXIT}
The meaning of each of the keywords that follow the WAITFOR keyword is shown in the following list:
In the following example of a WAITFOR statement, a DELAY is used to specify that a pause of forty seconds is taken before the subsequent SELECT statement is executed:
waitfor delay '00:00:40' select * from employees
In the second WAITFOR example, a TIME is used to wait until 3:10:51 PM of the current day until the subsequent SELECT statement is executed.
waitfor time '15:10:51' select * from employees
You can use a CASE expression to make an execution decision based on multiple options. Using the CASE construct, you can create a table that will be used to lookup the results you are testing and apply them to determine what course of action should be taken. The syntax of the CASE expression is as follows:
CASE [expression] WHEN simple expression1|Boolean expression1 THEN expression1 [[WHEN simple expression2|Boolean expression2 THEN expression2] [...]] [ELSE expressionN] END
If you use a comparison operator in an expression directly after the CASE keyword, the CASE expression is called a searched expression rather than a simple CASE expression. You can also use a Boolean operator in a searched CASE expression.
In a simple CASE expression, the expression directly after the CASE keyword always exactly matches a value after the WHEN keyword. In Listing 14.25, a CASE expression is used to substitute alternate values for the column department in the table company. In the following example, a CASE expression is used to return a corresponding set of alternate values for three department values of the table company.
select name,division= case department when "Sales" then "Sales & Marketing" when "Field Service" then "Support Group" when "Logistics" then "Parts" else "Other department" end, badge from company go name division badge -------------------- ----------------- ----------- Fred Sanders Sales & Marketing 1051 Bob Smith Sales & Marketing 1834 Mark McGuire Support Group 1997 Stan Humphries Support Group 3211 Sue Sommers Parts 4411 Lance Finepoint Other department 5522 Fred Stanhope Support Group 6732 Ludmilla Valencia Other department 7773 Jim Walker Other department 7779 Jeffrey Vickers Other department 8005 Barbara Lint Support Group 8883 Sally Springer Sales & Marketing 9998 (12 row(s) affected)
If you don't use an ELSE as part of the CASE expression, a NULL is returned for each non- matching entry, as shown in Listing 14.26:
select name,division= case department when "Sales" then "Sales & Marketing" when "Field Service" then "Support Group" when "Logistics" then "Parts" end, badge from company go name division badge ---------------------------------------------- Fred Sanders Sales & Marketing 1051 Bob Smith Sales & Marketing 1834 Mark McGuire Support Group 1997 Stan Humphries Support Group 3211 Sue Sommers Parts 4411 Lance Finepoint (null) 5522 Fred Stanhope Support Group 6732 Ludmilla Valencia (null) 7773 Jim Walker (null) 7779 Jeffrey Vickers (null) 8005 Barbara Lint Support Group 8883 Sally Springer Sales & Marketing 9998 (12 row(s) affected)
You'll recall that a searched CASE expression can include comparison operators and the use of AND as well as OR between each Boolean expression to permit an alternate value to be returned for multiple values of the column of a table. Unlike a simple CASE expression, each WHEN clause is not restricted to exact matches of the values contained in the table column.
In Listing 14.27, comparison values are used in each WHEN clause to specify a range of values that are substituted by a single alternative value:
select "Hours Worked" = case when hours_worked < 40 then "Worked Insufficient Hours" when hours_worked = 40 then "Worked Sufficient Hours" when hours_worked > 60 then "Overworked" else "Outside Range of Permissible Work" end from pays go Hours Worked --------------------------------- Worked Sufficient Hours Worked Sufficient Hours Overworked Worked Insufficient Hours Overworked Worked Sufficient Hours Overworked Worked Sufficient Hours Outside Range of Permissible Work Worked Insufficient Hours Worked Sufficient Hours Worked Sufficient Hours (12 row(s) affected)
NOTE: When a CASE construct is executed, only the first matching solution is executed.
CAUTION: You must use compatible datatypes for the replacement expression of the THEN clause. If the replacement expression of a THEN clause is a datatype that is incompatible with the original expression, an error message is returned.For example, a combination of original and replacement datatypes is compatible if the one is a variable length character datatype (VARCHAR) with a maximum length equal to the length of a fixed length character datatype (CHAR). In addition, if the two datatypes in the WHEN and THEN clauses are integer and decimal, the resultant datatype returned will be decimal in order to accommodate the whole and fractional portion of the numeric value.
You can also use both the COALESCE and NULLIF functions in a CASE expression. You use the COALESCE function to return a replacement value for any NULL or NOT NULL values that are present in, for example, the column of a database table. The syntax of one form of the COALESCE function is:
COALESCE (expression1, expression2)
In Listing 14.28, the COALESCE function is used to display either the product of hours_worked times rate or a zero if the columns hours_worked and rate are NULL:
select badge, "Weekly Pay in Dollars"=coalesce(hours_worked*rate,0) from pays2 go badge Weekly Pay in Dollars ----------- --------------------- 3211 400 6732 360 4411 520 5522 429 1997 510 9998 320 7773 550 8883 360 8005 420 7779 407 1834 400 1051 360 3467 0 3555 0 7774 0 (15 row(s) affected)
NOTE: A COALESCE function is equivalent to a searched CASE expression where a NOT NULL expression1 returns expression1 and a NULL expression1 returns expression2. An equivalent CASE expression to a COALESCE function is as follows:CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END
You can use a COALESCE function as part of a SELECT statement as an alternative way of returning an identical display or because you find the COALESCE function simpler to use.
You can also use a NULLIF function with or in place of a CASE expression. The NULLIF function uses the following syntax:
NULLIF (expression1, expression2)
In Listing 14.29, a simple SELECT statement is first used to display the table without using a NULLIF function to show all column values for all rows. A second SELECT statement is used to operate on the columns, badge, and old_badge.
select * from company2 go name department badge old_badge -------------------- -------------------- ----------- ----------- Mark McGuire Field Service 1997 (null) Stan Humphries Field Service 3211 (null) Sue Sommers Logistics 4411 (null) Fred Stanhope Field Service 6732 (null) Ludmilla Valencia Software 7773 (null) Jim Walker Unit Manager 7779 (null) Jeffrey Vickers Mailroom 8005 (null) Fred Sanders SALES 1051 1051 Bob Smith SALES 1834 1834 Sally Springer Sales 9998 9998 Barbara Lint Field Service 8883 12 Lance Finepoint Library 5522 13 (12 row(s) affected) select name,nullif(old_badge,badge) from company2 go name -------------------- ----------- Mark McGuire (null) Stan Humphries (null) Sue Sommers (null) Fred Stanhope (null) Ludmilla Valencia (null) Jim Walker (null) Jeffrey Vickers (null) Fred Sanders (null) Bob Smith (null) Sally Springer (null) Barbara Lint 12 Lance Finepoint 13 (12 row(s) affected)
The example only returns non-null values for rows that contain old_badge values that are different than new column values. In addition, a NULL is returned if no old column values were present. You can combine the use of the NULLIF and COALESCE functions to display the returned information in a more organized way.
Listing 14.30 combines a COALESCE and NULLIF function to return an old badge number only if it was different than the current badge number or if it was defined. If not, a new badge number is displayed.
select name,badge=coalesce(nullif(old_badge,badge),badge) from company2 go name badge -------------------- ----------- Mark McGuire 1997 Stan Humphries 3211 Sue Sommers 4411 Fred Stanhope 6732 Ludmilla Valencia 7773 Jim Walker 7779 Jeffrey Vickers 8005 Fred Sanders 1051 Bob Smith 1834 Sally Springer 9998 Barbara Lint 12 Lance Finepoint 13 (12 row(s) affected)
Stored procedures are nearly always the backbone of your system. You'll find that they make a good scaling point to move functionality from the client to the server. In cases where you find that you're repeating an SQL Server access over and over, consider moving it to a stored procedure and calling it from the application.
Perhaps one of the biggest benefits, of using stored procedures, for a software development house is the division of work between the development of the client application and the development of the server-side components. This was especially true in one case where an application was developed for an insurance company. Both the user interface and the database management were a challenge to implement.
By breaking development between the database and the client-side UI, it was possible to bring the project in on time, but still maintain experts in the development of the respective sides. The only thing, for example, that the UI development team knew about the database was the set of calls they needed to make (stored procedures) to get access to the information they needed. They didn't worry about the complicated search algorithms, the database management, or the rules implementations that were necessary behind the scenes.
On the other hand, the only thing the database team had to know about the user interaction with the application was the required response time and what the incoming information would look like. They didn't need to worry about what the dialog boxes looked like, or how the user set up the application.
If you think about it, you're breaking the development cycle into components as you do your application: client and server. You should use client-development experts for the tasks at which they are best--designing the interface, developing reports, and working with the users. Use the server-side developers for what they are best at--developing a solid database plan, implementing the server-side enforced rules, and so on. It provides you with real leverage on the personnel and project development cycles.
In this chapter, you've seen how you can use Transact-SQL to control the flow of your SQL Server-based application. Remembering to use these techniques to manipulate information on the server can significantly improve performance of your application.
Here are some other areas of interest relating to the materials covered here:
© Copyright, Macmillan Computer Publishing. All rights reserved.