Working with Stored Procedures | SQL Tips and Techniques

Table of contents:

Understanding Stored Procedures

As mentioned throughout the book, standard SQL is not a complete programming language. Rather, SQL is a non-procedural, data sublanguage with statements that let you create and delete databases and database objects; specify access privileges; enter, update, and delete data; and retrieve data for output to external applications through queries. Because SQL statements are often embedded in programs or executed as function calls using an application program interface (API), it is sometimes hard to tell where SQL ends and the procedural portion of the programming language begins.

However, standard SQL is data oriented and has no control structures for looping, keywords for conditional execution, or even a mechanism for executing multiple statements as a block. True, you can execute multiple statements as part of an open transaction. While a programming language lets you specify that a set of statements are to be executed in a specific sequence to carry out a multi-step task, an SQL transaction only identifies work to be performed by a set of autonomous statements that can be either undone or committed permanently to the database.

A stored procedure, unlike a transaction, is a sequence of Transact-SQL statements arranged in a specific order that you can assign a name, compile, and store on the MS-SQL Server. Once compiled and stored by the DBMS, you can use an application program (such as the MS-SQL Server's SQL Query Analyzer) to tell the MS-SQL Server to execute the statements in the stored procedure with a single command-similar to calling a subroutine within an application program. Moreover, also like subroutines in an application, stored procedures give you a way of executing a set of SQL statements in a specific order to carry out a multi-step task. In short, stored procedures let you use SQL statements to write "programs"-or at least multi-step subroutines you can call to perform database-related application processing within the database itself.

For example, you might use a stored procedure to transfer funds from a line of credit or savings account to a checking account if the MS-SQL Server invokes a trigger, because a check presented for processing would make the customer's checking account balance go negative. Similarly, a stored procedure might be called to accept a customer order, which requires an update of the quantities on-hand for several products in the inventory tables, followed by the insertion of records into the shipping and invoice tables, and of course, updates to the balances in the customer and salesperson tables.

Thus, stored procedures enhance standard SQL with several capabilities normally associated with programming languages (and missing from the SQL data sublanguage). These added capabilities include:

Conditional execution- After placing a set of Transact-SQL statements in a stored procedure, you can use the Transact-SQL If-Then-Else structure to decide which of the stored procedure's statements to execute based on results returned on other statements in the stored procedure.
Looping control structures- Transact-SQL's WHILE and FOR statements let you execute a sequence of statements repeatedly until some terminating condition is satisfied.
Named variables- You can use named memory locations (i.e. variables) in a stored procedure to hold values passed to the procedure through parameters in the stored procedure's heading, returned by queries within the stored procedure, or calculated by some other means.
Named procedures- After placing one or more Transact-SQL statements in a stored procedure and adding the desired Transact-SQL conditional execution and loop control structures, you can give the stored procedure a name and pass data to and from it through formal input and output parameters. In short, a stored procedure has the look and feel of a subroutine in a procedural or object-oriented programming language. Moreover, once defined and compiled, you can call the stored procedure by name in a trigger, interactively through the MS-SQL Server SQL Query Analyzer, from an application program, or as one of the clauses or predicates in a standard SQL statement.
Statement blocking- By calling the stored procedure as you would a subroutine in a program, you can cause the DBMS to execute a sequence of SQL statements as if they were a single statement that performs several different yet related tasks.

In addition to extending the capabilities of standard SQL to make it more of a programming language than a pure data sublanguage, stored procedures also offer the following benefits over interactive SQL statement execution:

Encapsulation- In the world of object-oriented programming, stored procedures are methods that can be used to manipulate database objects. By using SQL security to disallow all direct access to SQL statements and database objects, you can encapsulate both statements and objects within stored procedures. Forcing everyone to use only stored procedures to work with the data in the database can prevent users from circumventing business rules by not applying them or skipping integrity checks in application programs that work with the database. Moreover, stored procedures make it possible for users that know nothing about database objects, structures, or even SQL statement execution, to work with the database in a safe manner. To call a stored procedure, a user only needs to know the input and output parameters in the procedure call and an understanding of the stored procedure's purpose.
Improved performance- Each time you submit an SQL statement to the MS-SQL Server for execution, the DBMS must parse, validate, optimize, and generate an execution plan before it can execute the statement. By compiling stored procedures and then storing them on the SQL server, you can parse, validate, optimize, and generate an execution plan for the entire sequence of SQL statements in the stored procedure in advance. As such, when you call the stored procedure at run-time, the DBMS can execute the statements in the stored procedure more quickly because it can go directly to statement execution, bypassing the parse, validate, optimize, and execution plan generation steps, which it performed earlier.
Reduced network traffic- Each time you submit an SQL statement to the DBMS for execution, your workstation must send the statement across the network to the MS-SQL Server, and the DBMS must return the statement's results set to your workstation. This back and forth traffic between workstation and SQL server can lead to network congestion if you have an SQL statement batch whose statements generate large results sets. Conversely, if you call a stored procedure that contains the same batch of statements, the DBMS executes the statements on the server and only sends the final results set, or perhaps only a single value, back to the workstation that called the stored procedure.
Reusability- After you compile a stored procedure, many users and application programs can execute the same sequence of statements without having to retype and resubmit them to the DBMS. Executing already debugged and tested SQL statement batches reduces the risk of introducing programmer error when performing work that requires many SQL statements or statements with complex logic.
Security- If the database owner (DBO) or system administrator (sa) compiles and stores a stored procedure, the stored procedure will have all access privileges on the database objects it uses. Therefore, the system administrator can grant minimal access privileges on database objects to individual users. Instead of letting users work with database objects directly, the SA can control the way in which work is accomplished by granting users execute privilege on the stored procedures that they need to do their jobs. The stored procedures let the users manipulate the data in the only in a pre-approved manner-as defined in the stored procedure's methods.

The next two tips will show you how to create and execute stored procedures, and Tips 533-537 will show you how to pass data into and out of stored procedures through variables and server based cursors.

Using the CREATE PROCEDURE Statement to Create a Stored Procedure

A CREATE PROCEDURE statement lets you create, compile, and save a stored procedure on an MS-SQL Server. By default, only the database owner (DBO) has CREATE PROCEDURE access to the database. However, the DBO can execute a GRANT in the form

GRANT CREATE PROCEDURE

to grant CREATE PROCEDURE access to the user ID identified by .

The syntax of the CREATE PROCEDURE statement is

CREATE PROCEDURE  [; ]
 [{@  [VARYING]
 [=][OUTPUT]
 [...,n]}]
 [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
 [FOR REPLICATION]
AS

where:

is the name of the stored procedure and can be, at most, 128 characters in length.
allows you to create multiple stored procedures with the same name. You can execute a specific version of the stored procedure by including the version number in the execute command, as in: EXEC usp_proc;2. If you call a stored procedure without specifying a version number, the DBMS will execute the stored procedure with the highest version number in the group of stored procedures with the same name.

You can also drop a specific version of a stored procedure by including the version number in the DROP statement as in: DROP PROCEDURE usp_proc;2 or you can drop all versions of the stored procedure at once by omitting the version number, as in: DROP PROCEDURE usp_proc.
is the name of a parameter that can be used as a variable within the stored procedure. The value of each parameter must be supplied in the stored procedure call or set to a default value as part of the CREATE PROCEDURE statement. While parameters can be used as variable names within the stored procedure, parameters cannot be used as column names, table names, or the names of other database objects.
is the parameter's data type. A parameter may be any of the valid SQL data types (including TEXT, NTEXT, and IMAGE) or a user-defined data type. If the parameter is data type CURSOR, the parameter must also be specified as VARYING and OUTPUT.
VARYING is only valid with parameters of data type CURSOR. Specifies that the parameter will contain a result set whose content will vary and will be constructed dynamically by statements within the stored procedure.
is the default (initial) value for a parameter. If specified, the procedure can be executed without specifying the value of the parameter.
OUTPUT indicates that the value of the parameter may be changed within the stored procedure and that the modified value is to be returned to the calling procedure.
...,n indicates that the CREATE PROCEDURE statement can have up to 2,100 parameters.
RECOMPILE tells the MS-SQL Server to compile (i.e., generate a new execution plan) for the stored procedure each time it is called. Without the RECOMPILE option, the DBMS will compile the stored procedure when it executes the CREATE PROCEDURE statement and will use the same execution plan each time the stored procedure is called.
ENCRYPTION tells the MS-SQL Server to encrypt the text of stored procedure's entry in the SYSCOMMENTS table to prevent users from viewing the statements in a compiled stored procedure. Specifying the ENCRYPTION option also prevents the stored procedure from being published as part of the MS-SQL Server replication process.
FOR REPLICATION is the stored procedure that can only be executed during the replication process. You cannot specify both RECOMPILE and FOR REPLICATION in the same CREATE PROCEDURE statement.
is the Transact-SQL statements. The maximum size of a stored procedure is 128MB.

For example, to create a stored procedure that displays the contents of an EMPLOYEES file, you could execute a CREATE PROCEDURE statement such as:

 CREATE PROCEDURE usp_show_all_employees
 AS SELECT * FROM employees

Note

You should come up with a standard naming convention for stored procedures. Unless you create and store a stored procedure with the other stored procedures in the MASTER database, avoid the temptation to prefix your stored procedures with an "sp_" like the system stored procedures. After receiving the command to execute a stored procedure that starts with "sp_", the MS-SQL Server first looks for the stored procedure in the MASTER database. The search can be quite time consuming at worst (if there are many stored procedures in the MASTER database), and is wasteful at best, since the stored procedure you want to execute is not located in the MASTER database. Use a standard prefix such as "usp_" for user-stored procedures. If you do so, the DBMS will search the current database when a user calls the stored procedure, thus avoiding the time wasted searching the MASTER database first.

When the DBMS executes a CREATE PROCEDURE statement, the DBMS parses, validates, optimizes, and generates an execution plan for each of the statements in the stored procedure. If any of the stored procedure's statements are syntactically incorrect or refer to nonexistent database objects (other than stored procedures), the DBMS will reject the stored procedure and report the errors it found so you can correct the statements and resubmit the updated CREATE PROCEDURE statement to the DBMS for execution. If one or more statements in the stored procedure refer to other, as yet unimplemented (i.e. currently nonexistent) stored procedures, the DBMS will generate a warning message, but will still compile and install the stored procedure on the MS-SQL Server.

Note

When validating the statements in a stored procedure, the DBMS does not take access privileges on database objects referenced in the stored procedure's statements into account. As such, a user could execute a CREATE STORED procedure statement and create a stored procedure the user cannot then execute.

Using the EXECUTE Statement to Call a Stored Procedure

After creating a stored procedure (as you learned to do in the previous tip), you can tell the DBMS to execute the Transact-SQL statements in the stored by procedure by:

Submitting an SQL statement execution request that calls the stored procedure from within an application program.
Entering the stored procedure's name as the first line in a statement batch.
Submitting an EXECUTE (or EXEC) statement that names the stored procedure during an interactive session, while in another stored procedure, or in a TRIGGER.
Using the stored procedure's name as an argument in an SQL statement.

For example, to call a stored procedure without parameters (such as the USP_ SHOW_ALL_EMPLOYEES stored procedure created near the end of the previous tip), simply type EXECUTE (or EXEC) followed by a space and the procedure name, as in

 EXECUTE usp_show_all_employees

or:

 EXEC usp_show_all_employees

After submitting either of the preceding two statements to the DBMS for execution, the DBMS will execute the statements in the stored procedure named USP_ SHOW_ALL_EMPLOYEES.

If you do not own the stored procedure, but have been granted EXECUTE rights on it, you must include the owner's username in the EXECUTE statement, as in:

 EXEC frank.usp_show_all_employees

This tells the DBMS to execute the USP_SHOW_ALL_EMPLOYEES stored procedure owned by username FRANK.

Note

If you have EXECUTE privilege on a stored procedure owned by the database owner (DBO), you do not have to specify "DBO" in the stored procedure's name when you use the EXECUTE statement to call it. The DBMS automatically searches the list of stored procedures you own and those owned by the DBO.

If the stored procedure has a parameter list, include parameter values after the procedure's name in the EXECUTE statement. List the parameter values in the order in which they appear in the stored procedure's CREATE statement. Separate parameters in the list with a comma, and enclose character string values in quotes.

For example, to call stored procedure created by

 CREATE PROCEDURE usp_add_employee @first_name VARCHAR(30),
 @last_name VARCHAR(30),
 @address VARCHAR(30),
 @office INTEGER = 1,
 @manager INTEGER = 1
 AS INSERT INTO employees
 (first_name, last_name, address, office, manager)
 VALUES
 (@first_name, @last_name, @address, @office, @manager)

submit an EXEC statement such as:

 EXEC usp_add_employee 'Wally', 'Wallberg',
 '777 Sunset Strip', 5

If you prefer, you can explicitly specify the parameter names in the procedure call, which allows you to list the parameters in any order you wish. For example, the procedure call

EXEC usp_add_employee @office = 5,
 @address = '777 Sunset Strip',
 @first_name = 'Wally', @last_name = 'Wallberg'

is equivalent to the preceding procedure call that assigns parameter values by position.

Understanding How to Declare Variables Within a Stored Procedure

In addition to the parameters passed into a stored procedure, you may find it necessary or at least convenient, to define additional variables to hold intermediate values during the procedure's execution. The SQL DECLARE statement lets you create one or more local variables the DBMS can use while executing the statements in a stored procedure.

Although not required, declare the variables you plan to use at the beginning of the stored procedure—between the end of the stored procedure's heading and its first Transact-SQL statement.

The syntax of the DECLARE statement used to create variables for use in a stored procedure is:

 DECLARE @ 
 [ , @ ]

For example, the following stored procedure declaration creates three local variables: @AUTH_COUNT, @TOT_SALES, and @AUTH_TOT_SALES.

 CREATE PROCEDURE usp_compare_author_sales @author_ID ID
 AS
 /*** DECLARE local variables ***/
 DECLARE @auth_count INTEGER,
 @tot_sales MONEY,
 @auth_tot_sales MONEY

 /*** Count the number of authors ***/
 SELECT @auth_count = COUNT(*) FROM authors

 /*** Compute the total sales YTD for all authors ***/
 SELECT @tot_sales = SUM(ytd_sales) FROM titles
 WHERE ytd_sales IS NOT NULL

 /*** Compute the total sales YTD for a specific author ***/
 SELECT @auth_tot_sales = SUM(ytd_sales)
 FROM titles t, titleauthor ta
 WHERE t.title_ID = ta.title_ID
 AND au_ID = @author_ID
 /*** Display the results ***/
 SELECT @auth_count 'authors',
 @tot_sales / @auth_count 'avg YTD sales / author',
 au_ID, @auth_tot_sales 'Total Sales',
 (au_lname + ', ' + au_fname) 'Name'
 FROM authors WHERE au_ID = @author_ID

Notice that the name of each variable declared in a stored procedure, like the name of each parameter passed to the stored procedure, begins with an "at" sign (@). Also like parameters, variables declared in a stored procedure can be of any SQL or user-defined data type.

SELECT statements can be used to assign values to variables and to display the values of variable's and other table data.

Using Stored Procedure Parameters to Return Values

Within the four preceding tips, you learned how to create and call stored procedures. As you learned in Tip 530 "Understanding Stored Procedures," you use a stored procedure to execute a sequence of statements arranged in a specific order. Once compiled and stored by the DBMS, you can execute the statements within a stored procedure by submitting it to the DBMS using the keyword EXEC (or EXECUTE) followed by the stored procedure's name.

For example, to execute the stored procedure USP_PROCESS_CHECK, you might submit the following statement to the DBMS:

EXEC usp_process_check 112233, 123, 258.59

You use parameters to pass values to statements within a stored procedure. A stored procedure's parameters, like other variables, are named memory locations in which you can store values. In this example, 112233, 123, and 258.59 are input parameters in that they pass values into the stored procedure. Thus, given the following declaration, the stored procedure call in this example passes 112233 to statements within the stored procedure as @ACCOUNT_NUMBER, 123 as @CHECK_NUMBER, and 258.59 as @CHECK_ AMOUNT:

CREATE PROCEDURE usp_process_check @account_number INTEGER,
 @check_number INTEGER,
 @check_amount REAL
AS
 INSERT INTO checks (@account_number, @check_number,
 @check_amount)

While input parameters let you pass values into a stored procedure, you use output parameters to return values to the caller. Suppose the stored procedure in this example updated the customer's checking and line of credit balances, in addition to storing check information within the CHECKS table. The stored procedure declaration might then be as follows:

 CREATE PROCEDURE usp_process_check
 @account_number INTEGER,
 @check_number INTEGER,
 @check_amount REAL,
 @checking_balance REAL OUTPUT,
 @loc_used REAL OUTPUT,
 @loc_balance REAL OUTPUT
 AS
 SET @loc_used = 0

 /* Retrieve current account balances */

 SELECT @checking_balance =
 (SELECT checking_balance FROM customers
 WHERE account_number = @account_number)

 SELECT @loc_balance =
 (SELECT loc_balance FROM customers
 WHERE account_number = @account_number)

 /* If check amount would overdraw the checking balance,
 tap the line of credit */
 IF @checking_balance < @check_amount
 SET @loc_used = (@check_amount - @checking_balance)

 /* Store the check within the CHECKS table and update the
 customer's balance(s) */

 SET @loc_balance = @loc_balance + @loc_used
 SET @checking_balance =
 @checking_balance - @check_amount + @loc_used

 INSERT INTO checks
 VALUES (@account_number, @check_number, @check_amount)

 UPDATE customers
 SET loc_balance = @loc_balance,
 checking_balance = @checking_balance
 WHERE account_number = @account_number

In this example, @CHECKING_BALANCE, @LOC_USED, and @LOC_BALANCE are output parameters, meaning the stored procedure will pass the changes it makes to the values of these parameters within the stored procedure back to the statement that invoked it. Don't be overly concerned with what the stored procedure in this example does. The important thing to understand is that Transact-SQL lets you designate input and output parameters. A parameter is an output parameter (that is, able to pass an updated value back to the stored procedure's caller) only when the keyword OUTPUT follows the parameter's data type declaration in the CREATE PROCEDURE statement.

It seems that each DMBS product has its own way to designate output parameters. Oracle, for example, has you label parameters as in, out, or in out and to place the designation prior to the parameter's data type declaration. Informix uses the keyword RETURNING to specify the parameters that are to return values. Thus, if you are using a DBMS other than MS-SQL Server, be sure to check your system documentation for the specific way in which you must specify which parameters are input and which are output within the stored procedure declaration.

Note

You can use pass values into a stored procedure through both input and output parameters. However, if the stored procedure changes the value of input parameters, their new values will not be returned to the caller when the stored procedure completes its execution. Conversely, any changes the stored procedure makes to output parameters will be passed back. Thus, input parameters are, in effect, one-way valves that let you pass values into a stored procedure but do not let you retrieve values from it. Conversely, output parameters allow the flow in both directions-initial values into the stored procedures and updated values back out to the caller.

To allow statements within a stored procedure to update the value of an output parameter, the procedure call must have an acceptable "target" that can receive the value to be returned.

For example, you might call USP_PROCESS_CHECK with an EXEC statement such as the following:

 EXEC usp_process_check 112233, 123, 258.59,
 @checking_balance OUTPUT, @loc_balance OUTPUT,
 @loc_used OUTPUT

To pass input parameter values, you can use either literal values (such as the first three parameters passed in this example), or you can pass the names of variables that contain the values you want passed within the input parameters. For output parameters however, you must use the names of variables (that is, names of memory locations and not literal values), because the stored procedure must have access to a memory location in which to place the parameters value before returning control to the stored procedure's caller. In addition, Transact-SQL requires that when calling a stored procedure you include the keyword OUTPUT after each variable whose value the stored procedure is allowed to change. If you omit the keyword OUTPUT, the DBMS will still execute the procedure call without error. However, the output parameters will retain the value they had prior to the stored procedure's execution even after they are changed within the stored procedure.

As noted previously, though certain parameters are designated as "output" parameters for Transact-SQL, output parameter communication is not truly one-way. In fact, Transact-SQL passes the current value of an output parameter into the stored procedure as well as passing its updated value back out to the stored procedure's caller. Therefore, in this example, if you removed the first two SELECT statements from within the stored procedure and executed them before calling it, you could pass the balances in @CHECKING_BALANCE and @LOC_BALANCE into the stored procedure. Whether initially set within the stored procedure or external to it, the two output parameters will hold the updated balance information (for the checking account and line of credit, respectively) when the stored procedure returns control to the statement that called it.

Returning a Value from a Stored Function Using the Keyword RETURN

When you want to execute a sequence of statements to produce a scalar (single) value or a table, use a stored function rather than a stored procedure. Unlike a stored procedure, which can return multiple values through output parameters, a stored function can return only a single value or a table (which, may contain multiple values). Moreover, a function cannot execute INSERT, UPDATE, DELETE, or other statements that modify database objects external to the stored function. However, if it returns a table, the stored function can execute INSERT, UPDATE, or DELETE statements that change the temporary table the function returns.

Thus, if you want to change the contents or structure of permanent database objects, or return multiple values through parameters, use a stored procedure. If you want to return a single value or set of values within a single table without changing any permanent database objects, use a function.

To create a function, you use the CREATE FUNCTION statement, which has the following syntax:

 CREATE FUNCTION [owner_name.]
 ([{@ [AS]
 [=]}[...,n]])
 RETURNS 
 [AS]
 BEGIN
 
 RETURN 
 END

In Tip 537 "Using the CREATE FUNCTION Statement to Create a Stored Function," you will learn the syntax for each of the three forms of the CREATE FUNCTION statement. The syntax differs depending on the type of value the function returns-one syntax for returning a scalar (as shown in the following code within this tip), a second to return an inline table, and a third for a multi-statement table return.)

For now, simply note the keyword RETURNS that follows the stored function's parameter list. When a stored function is to return a single (scalar) value, you specify the returned value's data type after the keyword RETURNS. Note too, the RETURN statement immediately before the keyword END, which ends the function's declaration. For functions that return a scalar value, you specify the value that the function returns immediately after the keyword RETURN either as a literal value, as a variable that contains the value, or as an expression.

Suppose, for example, that you want to make a list of salespeople who have generated over $100,000.00 in sales during their employment. You could submit a SELECT statement with a correlated subquery (as you learned to do in Tip 339 "Understanding Correlated Subqueries Introduced with Comparison Operators"). Or, you could encapsulate the subquery as a simple SELECT statement within a function. If you named the function that returns the total sales for an employee UFN_GET_TOTAL_SALES for example, your query will be similar to that shown here:

SELECT first_name, last_name,
 dbo.ufn_get_total_sales(salesrep_ID)
FROM employees
WHERE dbo.ufn_get_total_sales(salesrep_ID) > 100000.0

Given the following definition, the stored function UFN_GET_TOTAL_SALES returns a single value (the total sales made by a particular employee), each time it is called:

CREATE FUNCTION ufn_get_total_sales(@salesrep_ID INTEGER)
RETURNS REAL
BEGIN
 RETURN (SELECT SUM(order_total)
 FROM cust_orders
 WHERE cust_orders.salesrep_ID = @salesrep_ID)
END

Thus, the WHERE clause in the original query is satisfied only when the total orders booked by a sales rep (as returned by the stored function) are greater than $100,000.00.

As mentioned previously within this tip, you can write a function that returns a table of values rather than a single (scalar) value. Suppose, for example, that you want a list of all checks and deposits made by a particular customer. You might create the following function:

CREATE FUNCTION ufn_cust_trans(@cust_ID INTEGER)
RETURNS @trans_list TABLE
 (trans_date DATETIME,
 trans_ID INTEGER,
 trans_type CHAR(1),
 trans_amount REAL)
BEGIN
 INSERT @trans_list
 SELECT deposit_date, deposit_ID, 'D', deposit_amount
 FROM deposits
 WHERE deposits.cust_ID = @cust_ID
 ORDER BY deposit_date

 INSERT @trans_list
 SELECT check_date, check_number, 'C', check_amount
 FROM checks
 WHERE checks.cust_ID = @cust_ID
 ORDER BY check_date

 RETURN
END

When called, UFN_CUST_TRANS will return a table with a list of all deposits in date order followed by a list of all checks in order by check date. Note that when used to return a table, a stored function works much like a database VIEW. However, as shown by this example, whereas a database VIEW must be based on a single SELECT statement, you can use multiple SELECT statements (and perform other Transact-SQL processing) to generate the table returned by a stored function.

You can execute queries on a table returned by a function as you can on any other "normal" database table. For example, you might execute the following query to list all transaction within the table:

 SELECT * FROM ufn_cust_trns(123456)

Or, you might use aggregate functions to display the total count of deposits, checks, and the grand total of each:

SELECT trans_type, COUNT(*), SUM(trans_amount)
FROM ufn_cust_trans(123456)
GROUP BY trans_type

Note

When calling a user-defined function that returns a scalar value, you must include the owner's name-even if the stored function is one you created under your own user-name. Unlike stored procedures, the DBMS searches neither the list of stored functions you own nor those owned by the DBO for a matching name if you omit the owner's ID from the function call. As such, the DBMS will report an error such as

'ufn_get_total_sales' is not a recognized function name'

if you omit the owner ID from the function call as shown here:

PRINT ufn_get_total_sales(1)

You can however, call a function you own or one owned by the DBO without providing the owner's ID if the function returns a table (versus a scalar value), as shown by the second example earlier within this tip.

Working with Cursors in Stored Procedures

Operations within a relational database typically act on a complete set of rows at one time. When executing a SELECT statement, for example, the DBMS returns all rows that satisfy the search condition in the query's WHERE clause at once, versus returning one row at a time. Similarly, an UPDATE statement makes the same changes to all rows that satisfy the search criteria in its WHERE clause at once. Sometimes, however, you must work with data one row (or small block of rows) at a time rather than with all rows within the results set at the same time. You can use database cursors within a stored procedure when you must work with a results set row-by-row.

Suppose, for example, that you have a stored procedure USP_CLEAR_CHECKS, which must clear checks for your customers. To reduce the number of overdrafts, you want the procedure to apply the checks in ascending order by amount. (That way, the maximum number of checks will clear if the customer's balance won't quite cover the total amount of all checks written.) In this example, you want the checks to end up in one of two tables: CLEARED_ITEMS or OVERDRAFT_ITEMS, and you want to insert a $10.00 fee for each overdraft item into the FEES table.

To create and work with a database cursor within a stored procedure (or within a stored function, for that matter) use the same DECLARE CURSOR, OPEN, FETCH, and CLOSE statements you learned about when you worked with database cursors in Tips 427-445. For example, you might declare the USP_CLEAR_CHECKS procedure as shown here:

CREATE PROCEDURE usp_clear_checks
 @account_no INTEGER,
 @date_processed DATETIME,
 @checking_balance MONEY OUTPUT
AS
/* Create temporary variables into which to FETCH values
 with the columns values from a table row */

 DECLARE @check_amt MONEY
 DECLARE @check_date DATETIME
 DECLARE @check_no INTEGER

/* Declare the cursor in which to store the query results
 temporarily during row-by-row processing */

 DECLARE cur_unproc_checks CURSOR FOR
 SELECT check_date, check_no, check_amount
 FROM unprocessed_checks
 WHERE account_no = @account_no
 ORDER BY check_amount

/* OPEN the cursor and then FETCH the first row within the
 results set */

 OPEN cur_unproc_checks
 FETCH cur_unproc_checks
 INTO @check_date, @check no, @check_amt

/* If no rows within the results set (because the customer
 has no outstanding checks) CLOSE the cursor and RETURN
 to the caller */

 IF (@@fetch_status <> 0)
 BEGIN
 CLOSE cur_unproc_checks
 DEALLOCATE cur_unproc_checks
 RETURN
 END
 SET NOCOUNT ON

/* Work through the cursor one row (check) at a time. Check
 to make sure the last FETCH was successful, then process
 the row of results. After processing, FETCH the next row
 and repeat the process until the FETCH is unsuccessful-
 meaning there are no more rows to process. */

 WHILE (@@fetch_status = 0)
 BEGIN
 IF @checking_balance - @check_amt >= 0
 BEGIN
 SET @checking_balance =
 @checking_balance - @check_amt

 INSERT INTO cleared_items
 VALUES (@date_processed, @account_no,
 @check_date, @check_no, @check_amt)
 END
 ELSE
 BEGIN
 INSERT INTO overdraft_items
 VALUES (@date_processed, @account_no,
 @check_date, @check_no, @check_amt)

 INSERT INTO fees
 VALUES ('OD', @date_processed, @account_no,
 @check_date, @check_no, @check_amt,
 10.00)
 END

/* FETCH the next row from the results set */

 FETCH cur_unproc_checks
 INTO @check_date, @check_no, @check_amt
 END

 CLOSE cur_unproc_checks
 DEALLOCATE cur_unproc_checks
 RETURN

The preceding code represents the Transact-SQL statements you would use to process cursor results on an MS-SQL Server. If you are using a DBMS product other than MS-SQL Server, your statements will vary. In Oracle, for example, you would use a FOR loop to move through the cursor one row at a time and you will work directly with the cursor's column values without first transferring them to interim variables. Informix, on the other hand, will have you use a FOREACH loop to transfer the rows of values within a cursor, on row at a time, into temporary variables for processing. Therefore, check your DBMS system manual for the exact statements you must use to work with cursors in your DBMS. While the exact statements may differ, in general, cursor processing proceeds as follows:

Use the DECLARE CURSOR statement to associate a cursor with an SQL SELECT statement. (A cursor is in effect, a virtual table that lets you work with the results set returned by query one row at a time.)
Submit an OPEN (cursor) statement to the DBMS to execute the cursor's SELECT statement and fill (or populate) the cursor with the SELECT statement's results set.
Execute a series of FETCH statements to retrieve the rows within the cursor and process each row after you retrieve it.
Use the CLOSE (cursor) statement to "close" the cursor, and then DEALLOCATE the cursor to delete the cursor, thereby freeing up memory (and perhaps disk resources) used to hold the SELECT statement's results set temporarily.

Using the CREATE FUNCTION Statement to Create a Stored Function

A CREATE FUNCTION statement lets you create, compile, and save a stored procedure on an MS-SQL Server. By default, only the database owner (DBO) has CREATE FUNCTION access to the database. However, the DBO can execute a GRANT statement in the form

GRANT CREATE FUNCTION

to give CREATE FUNCTION access to the user ID identified by . When allowing users to create stored functions, bear in mind that when executed, the stored function has the owner's access privileges on database objects. Therefore, make sure the owner of the stored function has at least REFERENCES access to all database objects used within the stored function.

As you will learn within this Tip, the syntax of the CREATE FUNCTION statement has one of three forms depending on the data type the function is to return. Stored functions are limited to returning either a single, scalar value or a table.

To create a scalar function that returns a single (scalar) value versus a table of values, use the following syntax

CREATE FUNCTION [.]
 ([{@ [AS] 
 [=]}[...,n]])
RETURNS 
[WITH {ENCRYPTION |SCHEMABINDING}]
[AS]
BEGIN
 
 RETURN 
END

where:

is the username of the user that owns the user-defined function being created. The username you enter for must be an existing user ID. When creating a function, bear in mind that the stored function operates on database objects with its owner's access rights.
is the name of the stored function and can be at most 128 characters in length.
is the name of a parameter which can be used as a variable within the stored function. The value of each parameter must be supplied in the stored function call or set to a default value as part of the CREATE FUNCTION statement. (When the user invokes the function, he or she must supply the keyword DEFAULT in place of each parameter omitted from the function call.) While parameters can be used as variable names within the stored function, parameters cannot be used as column names, table names, or the names of other database objects.
is the parameter's data type. A parameter may be any of the valid SQL scalar data types (including TEXT, NTEXT, and IMAGE). User-defined data types, even when scalar, are not permitted. The stored function's "scalar data type only" restriction means none of the function's input parameters can be a table or cursor.
is the default (initial) value for a parameter. If specified, the user can call the function by specifying the keyword DEFAULT in place of the parameter.
...,n indicates that the CREATE FUNCTION statement can have up to 2,100 parameters.
ENCRYPTION tells the MS-SQL Server to encrypt the text of stored function's entry in the SYSCOMMENTS table to prevent users from viewing the statements in a compiled stored function.
SCHEMABINDING tells the MS-SQL Server to bind the function to the database objects it references. If bound to database objects, the objects the function references cannot be altered (using the ALTER statement) or removed (using the DROP statement). To remove the function's "binding," so you can alter or drop database objects referenced within the function, you must either DROP the function or execute an ALTER function statement with the SCHEMABINDING option not specified. To create a stored function with SCHEMABINDING, the following conditions must be true: any user-defined functions and views within the function must also be schema-bound; objects referenced by the function cannot have a two-part name; the stored function and the objects it references must be within the same database; and the user executing the CREATE FUNCTION statement must have REFERENCES rights on all database objects used within the function.
is the data type of the value that the stored function returns to its caller. Because all stored function parameters are "input" parameters, the function can return only a single scalar value (or a single table of values in the case of inline table or multi-statement table functions, whose syntax is discussed next). The value the function returns may be of any of the valid SQL scalar data types (including TEXT, NTEXT, and IMAGE). User-defined data types, even when scalar, are not permitted.
is the Transact-SQL statements. The maximum size of a stored procedure is 128MB. Unlike stored procedures, the statements within a stored function may not alter database objects defined outside the function (such as global cursors, tables, views, and so on) in any way.

For example, to create a stored function that returns the total sales for a particular employee, you could execute a CREATE FUNCTION statement such as:

 CREATE FUNCTION ufn_employee_total_sales
 (@salesrep_ID INTEGER)
 RETURNS REAL
 BEGIN
 RETURN (SELECT SUM(order_total)
 FROM cust_orders
 WHERE cust_orders.salesrep_ID = @salesrep_ID)
 END

To create an inline table-valued stored function—a function that returns a table whose columns are not defined within the function's RETURNS clause—use the following syntax:

 CREATE FUNCTION [.]
 ([{@ [AS] 
 [=]}[...,n]])
 RETURNS TABLE
 [WITH {ENCRYPTION|SCHEMABINDING)]
 [AS]
 RETURN [(]  [)]


In this form, the keyword TABLE within the CREATE FUNCTION statement's RETURNS clause indicates the function will return a table (of values) to the caller. Because there is no description of the table itself within the RETURNS clause, the table is defined by the SELECT statement within the RETURN clause at the end of the function.
For example, to create a function that returns a table of all customer information for customers assigned to a particular salesperson, you might use the following CREATE FUNCTION statement:



 CREATE FUNCTION ufn_employee_customer_list
 (@salesrep_ID INTEGER)
 RETURNS TABLE
 AS
 RETURN SELECT * FROM customers
 WHERE customers.salesperson = @salesrep_ID


The third form of the CREATE FUNCTION statement also defines a function that returns a table (versus a scalar value). However, while the definition of the inline function's table is given by a single SELECT statement (within the function's RETURN clause), the syntax for a multi-statement table function contains the table definition within the function's RETURNS clause as shown here:



CREATE FUNCTION [.]
 ([{@ [AS] 
 [=]}[...,nl])
RETURNS @ TABLE 
[WITH {ENCRYPTION|SCHEMABINDING}]
[AS]
BEGIN
 
 RETURN
END


For example, to create a function that returns a table with a list of all customers from three sales offices (each with its own customer list), you might use the following CREATE FUNCTION statement:



 CREATE FUNCTION ufn_composite_customer_list ()
 RETURNS @composite_customer_list TABLE
 (cust_ID INTEGER,
 first_name VARCHAR(30),
 last_name VARCHAR(30),
 sales_office SMALLINT,
 salesperson INTEGER)
 AS
 BEGIN
 /* Build the consolidated list from office 1 customers */

 INSERT @composite_customer_list
 SELECT cust_ID, first_name, last_name, 1, salesperson
 FROM Office1_Customers

 /* Add office 2 customers to the consolidated list */

 INSERT @composite_customer_list
 SELECT cust_ID, first_name, last_name, 2, salesperson
 FROM Office2_Customers

 /* Add office 3 customers to the consolidated list */

 INSERT @composite_customer_list
 SELECT cust_ID, first_name, last_name, 3, salesperson
 FROM Office3_Customers

 RETURN
 END


When it executes a CREATE FUNCTION statement, the DBMS parses, validates, optimizes, and generates an execution plan for each of the statements in the stored function. If any of the stored function's statements are syntactically incorrect or attempt to change, the structure of, or data within database objects external to the function, the DBMS will reject the stored function and report the errors it found. If the DBMS fails to create the stored function due to an error, correct the error reported and then resubmit the (updated) CREATE FUNCTION statement to the DBMS for execution. If one or more statements in the stored function refer to other, as yet unimplemented (i.e. currently nonexistent) stored functions, the DBMS will generate a warning message, but will still compile and install the stored function on the MS-SQL Server. Note that while a stored function can call other user-defined and built-in stored functions, a stored function cannot call a user-defined stored procedure.


Note 
Stored functions handle statement errors differently from stored procedures. If a Transact-SQL error causes the DBMS to stop executing a statement within a stored procedure, the DBMS continues statement execution at the next statement within the stored procedure. Conversely, if an error halts statement execution within a function, the DBMS returns immediately to the caller and stops executing the statement that invoked the stored function as well.








 




538 Using the MS SQL Server Enterprise Manager to View or Modify a Stored Procedure or Function
If you are using the MS-SQL Server DBMS, you can call the system-stored procedure SP_HELPTEXT with the following syntax to display the contents of a default, rule, view, or unencrypted stored function, procedure, or trigger:



 SP_HELPTEXT 


For example, to display the code for the stored procedure USP_PROCESS_CHECK (described in Tip 534 "Using Stored Procedure Parameters to Return Values"), you would call the SP_HELPTEXT stored procedure as:



 SP_HELPTEXT usp_process_check


If you want to modify (as well as view) the contents of a stored procedure or user-defined function, use the MS-SQL Server Enterprise manager to display the procedure or function by performing the following steps:


Click on the Windows Start button. Windows will display the Start menu.


Move your mouse pointer to Programs on the Start menu, select the Microsoft SQL Server option, and click your mouse on Enterprise Manager. Windows will start the Enterprise Manager in a new application window.



Click on the plus (+) to the left of SQL Server Group to display the list of MS-SQL Servers available on your network.


Click on the plus (+) to the left of the SQL server that has the database with the stored procedure or function you want to display (and perhaps modify). Enterprise Manager will display the Databases, Data Transformation, Management, Security, and Support Services folders for the SQL Server you selected.


Click on the plus (+) to the left of the Databases folder to display the list of databases managed by the MS-SQL Server, and then on the plus (+) to the left of the database with the stored procedure or function you want to display. For the current project, click on the plus (+) to the left of the SQLTips folder. MS-SQL Server will display icons for the tables, views, stored procedures, and other resources managed within the database.


To display a list of stored procedures within the database, click the Stored Procedures icon. Or, to display a list of stored functions, click the User-Defined Functions icon. The Enterprise Manager will use its right pane to display the list of the stored procedures or functions within the database you selected in Step 5.


Within the Enterprise Managers right pane, right-click your mouse on the stored procedure or function you want to view or modify. The Enterprise Manager will display a popup menu from which you will select Properties. For the current project, right-click your mouse on USP_PROCESS_CHECK534, and then select Properties from the pop-up menu to display the stored procedure's code as shown in Figure 538.1.



Figure 538.1: The MS-SQL Server Enterprise Manager Stored Procedure Properties dialog box




If you want to modify the stored procedure or function's behavior, make the desired changes, deletions, and additions in the within the Text window of the Stored Procedure (or User-Defined Function) Properties dialog box, and then click your mouse on the Check Syntax button.


Repeat Step 8 as necessary to correct any errors reported by the Enterprise Manager's syntax check of the procedure or function in the Text window.


To update the list of users allowed to execute the stored procedure or function, click the Permissions button. The Enterprise Manager will display the Object Properties dialog box with Permissions tab. Click a checkmark into the checkbox to the right of the usernames of those users allowed to execute the stored procedure. (To give all users permission to execute the stored procedure, click a checkmark into the checkbox to the right of the "public" role.) Then, click your mouse on the OK button to save your permissions updates and return to the Stored Procedure (or User-Defined Function) Properties dialog box.


To save your changes and make the updated stored procedure or function available to the DBMS, click your mouse pointer on the OK button.


After you complete Step 11, the Enterprise Manager will save the stored procedure (or user-defined function) text to disk and save it as a database object (by inserting the stored procedure or function text into the server's system tables). Then, the Enterprise Manager will exit the Stored Procedure (or User-Defined Function) Properties dialog box.
To create a new stored procedure or function, right-click your mouse on any unused space within the Enterprise Manager's right pane in Step 7. Then select New Stored Procedure (or New User-Defined Function) from the pop-up menu. Next, complete Steps 8–11, entering the stored procedure (or function) header and statements you want the DBMS to execute when the user calls the procedure or function in Step 8.


Note 
If you include the WITH ENCRYPTION clause within the CREATE PROCEDURE or CREATE FUNCTION statement, neither SP_HELPTEXT nor the Enterprise Manager will be able to display the stored procedure (or the User-Defined Function) source code. Moreover, you will not be able to use the Enterprise Manager to change the stored procedure or function's behavior by editing its text within the Stored Procedure (or User-Defined Function) Properties dialog box any time after saving the stored procedure or function to disk. In fact, the only way to change the behavior of an encrypted stored procedure or stored function is to drop the stored procedure or function and then recreate it. (You will learn how to delete stored procedures and functions within Tip 540 "Using the DROP Statement to Remove a Stored Procedure or User-Defined Function from the Database.")








 




539 Using Transact SQL Keywords DECLARE and SELECT to Define Variables in a Stored Procedure and Assign Initial Values to Them

When executing a batch of statements within a stored procedure or function, you often need variables to store data temporarily. As you learned in Tip 536 "Working with Cursors in Stored Procedures," you might use variables to store running totals as you work through a cursor, one row at a time. In addition, before you can use data stored within a cursor row, you must first transfer the data values from the cursor row's columns into temporary variables that your Transact-SQL statements can access.
To declare a variable, use the DECLARE statement with the following syntax:



 DECLARE @ 
 [...,@ ]


Thus, to declare variables in which to store INTEGER, REAL, and DATETIME values, you might write the following DECLARE statement:



 DECLARE @customer_count INTEGER, @total_sales REAL,
 @best_sales_date DATETIME


If you prefer, you can write multiple DECLARE statements within the same statement batch, or split a single declare statement across multiple lines so you can add comments about each variable's purpose such as:



DECLARE
 @customer_count INTEGER /* number of customers serviced
 by the salesperson */
 @total_sales REAL /* grand total sales made */
 @best_sales_date DATETIME /* date most sales made */


Note that a variable's name may consist of any number of letters and numbers, but must begin with an at sign (@).
To assign values to variables, use a SET or SELECT statement. For example, to initialize variables your statement batch might include statements such as the following:



SET @customer_count = 0
SET @total_sales = 0.00
SET @best_sales_date = ''


Similarly, you can use the SELECT statement to set variable values as shown here:



SELECT @customer_count = 0, @total_sales = 0.00,
 @best_sales_date = ''


Bear in mind that the SELECT statement, used to initialize variable values here, is the standard SQL SELECT statement. As such, you can write the SELECT statement using the following syntax:



 SELECT @variable_name = {expression | SELECT statement}
 [...{, @ =
 {expression | SELECT statement}}
 [FROM ]
 [WHERE expression]
 [GROUP BY ]
 [HAVING expression]
 [ORDER BY ]


Thus, to print the name of the customer that placed the largest order along with the ID and the total sales for that salesperson you might use the following statement batch:



DECLARE
 @customer_name VARCHAR(26)
 @largest_order MONEY /* largest order placed by a
 single customer */
 @total_sales MONEY /* grand total sales made */
 @salesperson_ID

SELECT @customer_name =
 RTRIM(first_name) + ' ' + RTRIM(last_name),
 @largest_order = order_total,
 @salesperson_ID = salesperson_ID
FROM customers
ORDER BY order_total

SELECT @total_sales = SUM(order__total)
FROM customers
WHERE salesperson_ID = @salesperson_ID

PRINT 'Largest order ('+CONVERT(VARCHAR(10),@largest_order)
 + ') placed by' +@customer_name+' Sales Rep: '
 +CONVERT (VARCHAR(6),@salesperson_ID)
PRINT 'Total sales for rep: '
 +CONVERT(VARCHAR(6),@salesperson_ID)+' = '
 +CONVERT(VARCHAR(10),@total_sales)


If the SELECT statement used to assign values to variables returns multiple rows, (as is the case with the first SELECT statement in this example), the DBMS assigns the variables the values from the last row in the results set returned.
Note that variables created using DECLARE statements are "local" variables. This means that after the DBMS executes the last statement in the statement batch, procedure, or function, variables declared within the batch, procedure, or function are no longer available for use.





 




540 Using the DROP Statement to Remove a Stored Procedure or User Defined Function from the Database

When you no longer need a stored procedure or stored function, you can remove it from the database with a DROP statement. Although the DBMS can manage a virtually unlimited number of stored procedures and functions, you should only keep online those currently used by system users.
During the lifetime of any DBMS, many once-important stored procedures and functions will fall into disuse as business conditions or data storage requirements change over time. By keeping obsolete procedures and functions in the database, you increase the amount of system documentation a user must review when searching for a procedure or function he or she needs. In addition, you increase server overhead by forcing the DBMS to search an ever-increasing list of stored procedures and functions each time a user invokes a stored procedure or function by name. Finally, (and perhaps most importantly), as table names are changed and tables are dropped from the database, some stored procedures may no longer function properly, because they attempt to operate on database objects that no longer exist.
To remove an existing stored procedure, use a DROP statement in the form:



DROP PROCEDURE 
 [..., )


Thus, to remove the stored procedures USP_PROCESS_CHECK, USP_ADD_ITEM, you would use a DROP PROCEDURE statement such as:



DROP PROCEDURE usp_process_check, usp_add_item




Note 
When removing a stored procedure you must drop all versions of the stored procedure at once. In Tip 531, "Using the CREATE PROCEDURE Statement to Create a Stored Procedure," you learned that you can create multiple versions of the same stored procedure within the same DBMS. Although you can modify and execute specific versions of a stored procedure by providing the version number when executing an ALTER PROCEDURE or EXEC statement, the DROP PROCEDURE statement does not allow you to enter a version number. Therefore, dropping a stored procedure means removing all its versions.



Similarly, to delete an existing user-defined function us a DROP statement of the form:



DROP FUNCTION 
 [..., ]


Therefore to remove the function UFN_REP_TOTAL_SALES, for example, you would submit the following statement to the DBMS for execution:



DROP FUNCTION ufn_rep_total_sales


DROP PROCEDURE and DROP FUNCTION rights default to the owner of the stored procedure or user-defined function, and are not transferable. However, any member of the SYSADMIN role, the DB_DDLADMIN (that is, the database data definition language administration) role, and the DBO can DROP any procedure or function by including the owner's ID in the DROP statement. For example, the DBO might remove a stored procedure owned by username "Konrad" with the following:



DROP PROCEDURE Konrad.usp_increase_my_pay







 



Chapter 22 Repairing and Maintaining MS SQL Server Database Files

SQL Tips and Techniques