Stored Procedures
If views raise the bar of database functionality, then stored procedures take it to the next level. Unlike views, stored procedures can be used for much more than reading data. They provide a wide range of programming functionality. Categorically, stored procedures can be used to do the following:
-
Implement parameterized views
-
Return scalar values
-
Maintain records
-
Process business logic
| Note | In the following examples, I've prefixed the names with the letters sp for stored procedure. If you use the Object Browser to view system stored procedure names, you'll see that most of these existing procedures are prefixed with sp_. It's not a good idea to use the same prefix as the system procedures because this is an indicator to the database engine to try to locate this procedure in the system catalog before it looks in your database. Although there are no specific compatibility issues with this prefix, this can degrade performance and cause potential confusion. |
Stored Procedures as Parameterized Views
Like views, stored procedures can be used to return a result set based on a SELECT statement. However, I want to clarify an important point about the difference between views and stored procedures. A view is used in a SELECT statement as if it were a table. A stored procedure is executed, rather than selected from. For most programming APIs, this makes little difference. If a programmer needs to return a set of rows to an application or report, ActiveX Data Objects (ADO) or ADO.NET can be used to obtain results from a table, a view, or a stored procedure.
A stored procedure can be used in place of a view to return a set of rows from one or more tables. Earlier in this chapter, I used a simple view to return selected columns from the Product table. Again, the script looks like this:
CREATE VIEW vProductCosts AS SELECT ProductID, ProductSubcategoryID, Name, ProductNumber, StandardCost FROM Product
Contrast this with the script to create a similar stored procedure:
CREATE PROCEDURE spProductCosts AS SELECT ProductID, ProductSubcategoryID, Name, ProductNumber, StandardCost FROM Product
To execute the new stored procedure, the name is preceded by the EXECUTE statement:
EXECUTE spProductCosts Although this is considered the most proper syntax, the following are also examples of acceptable syntax.
The shorthand version of EXECUTE:
EXEC spProductCosts No EXECUTE statement:
spProductCosts
Using Parameters
A parameter is a special type of variable used to pass values into an expression. Named parameters are used for passing values into and out of stored procedures and user-defined-functions. Parameters are most typically used to input, or pass values into, a procedure, but can also be used to return values.
Parameters are declared immediately after the procedure definition and before the term AS. Parameters are declared with a specific data type and are used as variables in the body of a SQL statement. I will modify this procedure with an input parameter to pass the value of the ProductSubCategoryID. This will be used to filter the results of the query. This example shows the script for creating the procedure. If the procedure already exists, the CREATE statement may be replaced with the ALTER statement:
ALTER PROCEDURE spProductCosts @SubCategoryID Int AS SELECT ProductID, Name, ProductNumber, StandardCost FROM Product WHERE ProductSubCategoryID = @SubCategoryID
To execute the procedure and pass the parameter value in SQL Query Analyzer or the Query Editor, simply append the parameter value to the end of the statement, like this:
EXECUTE spProductCosts 1 Alternatively the stored procedure can be executed with the parameter and assigned value like this:
EXECUTE spProductCosts @SubCategory = 1 Stored procedures can accept multiple parameters and the parameters can be passed in either by position or by value similar to the previous example. Suppose I want a stored procedure that filters products by subcategory and price. It would look something like this:
CREATE PROCEDURE spProductsByCost @SubCategoryID Int, @Cost Money AS SELECT ProductID, Name, ProductNumber, StandardCost FROM Product WHERE ProductSubCategoryID = @SubCategoryID AND StandardCost > @Cost
Using SQL, the multiple parameters can be passed in a comma-delimited list in the order they were declared:
EXECUTE spProductsByCost 1, $1000.00 Or the parameters can be passed explicitly by value. If the parameters are supplied by value it doesn't matter in what order they are supplied:
EXECUTE spProductsByCost @Cost = $1000.00, @SubCategoryID = 1 If a programmer is using a common data access API such as ADO or ADO.NET, separate parameter objects are often used to encapsulate these values and execute the procedure in the most efficient manner.
Although views and stored procedures do provide some overlap in functionality, they each have a unique purpose. The view used in the previous example can be used in a variety of settings where it may not be feasible to use a stored procedure. However, if I need to filter records using parameterized values, a stored procedure will allow me to do this where a view will not. So, if the programmer building the product browse screen needs an unfiltered result set and the report designer needs a filtered list of products based on a subcategory parameter, do I create a view or a stored procedure? That's easy, both. Use views as the foundation upon which to build stored procedures. Using the previous example, I select from the view rather than the table:
ALTER PROCEDURE spProductCosts @SubCategoryID Int As SELECT ProductID, Name, ProductNumber, StandardCost FROM vProductCosts WHERE ProductSubCategoryID = @SubCategoryID
The benefit may not be so obvious in this simple, one-table example. However, if a procedure were based on the seven-table vEmployeeContactDetail view, the procedure call might benefit from optimizations in the view design and the lower maintenance cost of storing this complex statement in only one object.
Returning Values
The parameter examples shown thus far demonstrate how to use parameters for passing values into a stored procedure. One method to return a value from a procedure is to return a single-column, single-row result set. Although there is probably nothing grossly wrong with this technique, it's not the most effective way to handle simple values. A result set is wrapped in a cursor, which defines the rows and columns, and may be prepared to deal with record navigation and locking. This kind of overkill reminds me of a digital camera memory card I recently ordered from a discount electronics supplier. A few days later, a relatively large box arrived and at first appeared to be filled with nothing more than foam packing peanuts. I had to look carefully to find the postage-size memory card inside.
In addition to passing values into a procedure, parameters can also be used to return values for output. Stored procedure parameters with an OUTPUT direction modifier are set to store both input and output values by default. Additionally, the procedure itself is equipped to return a single integer value without needing to define a specific parameter. The return value is also called the return code and defaults to the integer value of 0. Some programming APIs such as ADO and ADO.NET actually create a special output parameter object to handle this return value. Suppose I want to know how many product records there are for a specified subcategory. I'll pass the SubCategoryID using an input parameter and return the record count using an output parameter:
CREATE PROCEDURE spProductCountBySubCategory @SubCategoryID Int, @ProdCount Int OUTPUT AS SELECT @ProdCount = COUNT(*) FROM Product WHERE ProductSubCategoryID = @SubCategoryID
To test a stored procedure with output parameters in the Management Studio or Query Analyzer environments, it is necessary to explicitly use these parameters by name. Treat them as if they were variables but you don't need to declare them. When executing a stored procedure using SQL, the behavior of output parameters can be a bit puzzling because they also have to be passed in. In this example, using the same stored procedure, a variable is used to capture the output parameter value. The curious thing about this syntax is that the assignment seems backwards. Remember that the OUTPUT modifier affects the direction of the value assignment — in this case, from right to left:
DECLARE @Out Int EXECUTE spProductCountBySubCategory @SubCategoryID = 2, @ProdCount = @Out OUTPUT SELECT @Out AS ProductCountBySubCategory ProductCountBySubCategory ------------------------- 184
It is critical that the OUTPUT modifier also be added to the output parameter when it is passed in to the stored procedure. If you don't, the stored procedure will still execute, but it will not return any data.
DECLARE @Out Int EXECUTE spProductCountBySubCategory @SubCategoryID = 2, @ProdCount = @Out –-Missing the OUTPUT directional modifier SELECT @Out AS ProductCountBySubCategory ProductCountBySubCategory ------------------------- NULL
There is no practical limit to the number of values that may be returned from a stored procedure. The stated limit is 2,100, including input and output parameters.
If you need to return only one value from the procedure, this can be done without the use of an output parameter using the return code of the procedure as long as the value being returned is an integer. Here is the same stored procedure showing this technique:
CREATE PROCEDURE spProductCountBySubCategory @SubCategoryID Int AS DECLARE @Out Int SELECT @Out = Count(*) FROM Product WHERE ProductSubCategoryID = @SubCategoryID RETURN @Out
The RETURN statement does two things: it modifies the return value for the procedure from the default value, 0, and it terminates execution so that any statements following this line do not execute. This is significant in cases where there may be conditional branching logic. Typically the capture of the return value must be done with a programming API. Executing this stored procedure in Query Analyzer or Management Studio will not return any results because these interfaces don't display the procedure's return value by default.
Record Maintenance
Using stored procedures to manage the insert, update, and delete operations for each major database entity can drastically reduce the cost of data maintenance tasks down the road. Any program code written to perform record operations should do so using stored procedures and not ad-hoc SQL expressions. As a rule of thumb, when I design a business application, every table that will have records managed through the application interface gets a corresponding stored procedure to perform each of these operations. These procedures are by far the most straightforward in terms of syntax patterns. Although simple, writing this script can be cumbersome due to the level of detail necessary to deal with all of the columns. Fortunately, the SQL Server 2000 Query Analyzer and the SQL Server 2005 Management Studio include scripting tools that will generate the bulk of the script for you. Beyond creating the fundamental Insert, Update, Delete, and Select statements, you need to define and place parameters into your script.
Insert Procedure
The basic pattern for creating an Insert stored procedure is to define parameters for all non-default or auto-populated columns. In the case of the Product table, the ProductID primary key column will automatically be incremented because it's defined as an identity column; the rowguid and ModifiedDate columns have default values assigned in the table definition. The MakeFlag and FinishedGoodsFlag columns also have default values assigned in the table definition, but it may be appropriate to set these values differently for some records. For this reason, these parameters are set to the same default values in the procedure. Several columns are nullable and the corresponding parameters are set to a default value of null. If a parameter with a default assignment isn't provided when the procedure is executed, the default value is used. Otherwise, all parameters without default values must be supplied:
CREATE PROCEDURE spProduct_Insert @Name nVarChar(50) , @ProductNumber nVarChar(25) , @MakeFlag Bit = 1 , @FinishedGoodsFlag Bit = 1 , @Color nVarChar(15) = Null , @SafetyStockLevel SmallInt , @ReorderPoint SmallInt , @StandardCost Money , @ListPrice Money , @Size nVarChar(5) = Null , @SizeUnitMeasureCode nChar(3) = Null , @WeightUnitMeasureCode nChar(3) = Null , @Weight Decimal = Null , @DaysToManufacture Int , @ProductLine nChar(2) = Null , @Class nChar(2) = Null , @Style nChar(2) = Null , @ProductSubcategoryID SmallInt = Null , @ProductModelID Int = Null , @SellStartDate DateTime , @SellEndDate DateTime = Null , @DiscontinuedDate DateTime = Null AS INSERT INTO Product ( Name , ProductNumber , MakeFlag , FinishedGoodsFlag , Color , SafetyStockLevel , ReorderPoint , StandardCost , ListPrice , Size , SizeUnitMeasureCode , WeightUnitMeasureCode , Weight , DaysToManufacture , ProductLine , Class , Style , ProductSubcategoryID , ProductModelID , SellStartDate , SellEndDate , DiscontinuedDate ) SELECT @Name , @ProductNumber , @MakeFlag , @FinishedGoodsFlag , @Color , @SafetyStockLevel , @ReorderPoint , @StandardCost , @ListPrice , @Size , @SizeUnitMeasureCode , @WeightUnitMeasureCode , @Weight , @DaysToManufacture , @ProductLine , @Class , @Style , @ProductSubcategoryID , @ProductModelID , @SellStartDate , @SellEndDate , @DiscontinuedDate
It's a lot of script but it's not complicated. Executing this procedure in SQL is quite easy. This can be done in comma-delimited fashion or by using explicit parameter names. Because the majority of the fields and corresponding parameters are optional, they can be ommitted. Only the required parameters need to be passed; the optional parameters are simply ignored:
EXECUTE spProduct_Insert @Name = 'Widget' , @ProductNumber = '987654321' , @SafetyStockLevel = 10 , @ReorderPoint = 15 , @StandardCost = 23.50 , @ListPrice = 49.95 , @DaysToManufacture = 30 , @SellStartDate = '10/1/04'
The procedure can also be executed with parameter values passed in a comma-delimited list. Although the script isn't nearly as easy to read, it is less verbose. Even though this may save you some typing, it often becomes an exercise in counting commas and rechecking the table's field list in the Object Browser until the script runs without error.
EXECUTE spProduct_Insert 'Widget', '987654321', 1, 1, Null, 10, 15, 23.50, 49.95, Null, Null, Null, Null, 30, Null, Null, Null, Null, Null, '10/1/04'
When using this technique, parameter values must be passed in the order they are declared. Values must be provided for every parameter up to the point of the last required value. After that, the remaining parameters in the list can be ignored.
A useful variation of this procedure may be to return the newly generated primary key value. The last identity value generated in a session is held by the global variable, @@Identity. To add this feature, simply add this line to the end of the procedure. This would cause the Insert procedure to return the ProductID value for the inserted record.
RETURN @@Identity Of course, if you have already created this procedure, change the CREATE keyword to ALTER, make changes to the script, and then re-execute it.
Update Procedure
The Update procedure is similar. Usually when I create these data maintenance stored procedures, I write the script for the Insert procedure and then make the modifications necessary to transform the same script into an Update procedure. As you can see, it's very similar:
CREATE PROCEDURE spProduct_Update @ProductID Int , @Name nVarChar(50) , @ProductNumber nVarChar(25) , @MakeFlag Bit = 1 , @FinishedGoodsFlag Bit = 1 , @Color nVarChar(15) = Null , @SafetyStockLevel SmallInt , @ReorderPoint SmallInt , @StandardCost Money , @ListPrice Money , @Size nVarChar(5) = Null , @SizeUnitMeasureCode nChar(3) = Null , @WeightUnitMeasureCode nChar(3) = Null , @Weight Decimal = Null , @DaysToManufacture Int , @ProductLine nChar(2) = Null , @Class nChar(2) = Null , @Style nChar(2) = Null , @ProductSubcategoryID SmallInt = Null , @ProductModelID Int = Null , @SellStartDate DateTime , @SellEndDate DateTime = Null , @DiscontinuedDate DateTime = Null AS UPDATE Product SET Name = @Name , ProductNumber = @ProductNumber , MakeFlag = @MakeFlag , FinishedGoodsFlag = @FinishedGoodsFlag , Color = @Color , SafetyStockLevel = @SafetyStockLevel , ReorderPoint = @ReorderPoint , StandardCost = @StandardCost , ListPrice = @ListPrice , Size = @Size , SizeUnitMeasureCode = @SizeUnitMeasureCode , WeightUnitMeasureCode = @WeightUnitMeasureCode , Weight = @Weight , DaysToManufacture = @DaysToManufacture , ProductLine = @ProductLine , Class = @Class , Style = @Style , ProductSubcategoryID = @ProductSubcategoryID , ProductModelID = @ProductModelID , SellStartDate = @SellStartDate , SellEndDate = @SellEndDate , DiscontinuedDate = @DiscontinuedDate WHERE ProductID = @ProductID
The parameter list is the same as the Insert procedure with the addition of the primary key, in this case, the ProductID column.
Delete Procedure
In its basic form, the Delete procedure is very simple. The only necessary parameter is for the ProductID column value:
CREATE PROCEDURE spProduct_Delete @ProductID Int AS DELETE FROM Product WHERE ProductID = @ProductID
Handling and Raising Errors
A common choice you may need to make in many data maintenance procedures is how you will handle errors. Attempting to insert, update, or delete a record that violates constraints or rules will cause the database engine to raise an error. If this is acceptable behavior, you don't need to do anything special in your procedure code. When the procedure is executed, an error is raised and the transaction is aborted. You simply need to handle the error condition in the client program code. Another, often more desirable, approach would be to proactively investigate the potential condition and then raise a custom error. This may have the advantage of offering the user or client application more useful error information or a more graceful method to handle the condition. In the case of the Delete procedure, I could check for existing dependent records and then raise a custom error without attempting to perform the delete operation. This also has the advantage of not locking records while the delete operation is attempted.
Error Handling in SQL Server 2000
For many years, the ability to handle errors in Transact-SQL script has been limited to the same type of pattern used in other scripting languages. The query-processing engine is not equipped to respond to error conditions in the same way that an event-driven run-time engine would. What this boils down to is that if you suspect that an error might be raised after a specific line of script, you can check for an error condition and respond to it. The downside to this approach is that you have to be able to guess where an error might occur and be prepared to respond to it.
There are two general approaches to raising errors. One is to raise the error on-the-fly. This is done using a single statement. The other approach is to add custom error codes and message text to the system catalog. These messages can then be raised from script in any database on the server. Custom errors are added to the system catalog using the sp_AddMessage system stored procedure. Here's an example:
sp_AddMessage @msgnum=50010 , @severity=16 , @msgtext=’Cannot delete a , @with_ ,
Three parameters are required: the message number, message severity, and message text. There are also three additional optional parameters: one to specify logging the error in the server's application log, one for replacing a current error with the same message number, and one to specify the language of the error if multiple languages are installed on the server. Custom error numbers begin at 50,001. This is user-assigned and has no special meaning. It's just a unique value. The system recognizes severity values within specified numeric ranges and may respond by automatically logging the error or sending alerts. Alerts are configurable within the SQL Server Agent. Messages and errors are distinguished by the Severity Level flag. Those with a severity level from 0 to 10 are considered to be informational messages and will not raise a system exception. Those with a severity level from 11 to 18 are non-fatal errors, and those 19 or above are considered to be most severe. This scale was devised for Windows service error logging. The following table shows the system-defined error severity levels.
| Severity Level | Description |
|---|---|
| 1 | Misc. System Information |
| 2 - 6 | Reserved |
| 7 | Notification: Status Information |
| 8 | Notification: User Intervention Required |
| 9 | User Defined |
| 10 | Information |
| 11 | Specified Database Object Not Found |
| 12 | Unused |
| 13 | User Transaction Syntax Error |
| 14 | Insufficient Permission |
| 15 | Syntax Error in SQL Statements |
| 16 | Misc. User Error |
| 17 | Insufficient Resources |
| 18 | Fatal Error in Resource |
| 19 [*] | Fatal Error in Resource |
| 20 [*] | Fatal Error in Current Process |
| 21 [*] | Fatal Error in Database Processes |
| 22[*] | Fatal Error: Table Integrity Suspect |
| 23[*] | Fatal Error: Database Integrity Suspect |
| 24[*] | Fatal Error: Hardware Error |
| 25 [*] | Fatal Error |
|
[*]Messages with a severity level 19 or above will automatically be logged in the server's application log. When an error is raised in the procedure, you also have the option to explicitly log the message regardless of the severity level | |
When logging an error in the application log, errors with a severity level less than 14 will be recorded as informational. Level 15 is recorded as a warning, and levels greater than 15 are issued the error status.
This example demonstrates raising a previously declared error:
RAISERROR (50010, 16, 1) The output from this expression returns the message defined earlier:
Msg 50010, Level 16, State 1, Line 1 Cannot delete a product with existing sales orders.
The severity level is actually repeated in the call. I know, this seems like a strange requirement, but that's the way it works. It also does not have to be the same as the defined severity level. If I want to raise the error as a severity level 11 instead of the 16 I created it with, I can. The last parameter is the state. This value is user-defined and has no inherent meaning to the system, but it is a required argument. State can be a signed integer between –255 and +255. State can be used for internal tracking, for example, to track all "State 3" errors.
Here is an example of an ad-hoc message that has not been previously defined:
RAISERROR ('The sky is falling', 16, 1) The resulting output is as follows:
Msg 50000, Level 16, State 1, Line 1 The sky is falling
Note that ad-hoc messages use the reserved message id of 50000. Raising an ad-hoc message with a severity level of 19 or higher requires elevated privileges and must be performed with explicit logging. If you need to raise an error of this type, it's advisable to define these messages ahead of time.
When an error occurs, the global variable, @@ERROR, changes from its default value of 0 to an integer type standard error number. SQL Server 2000 can return more than 3800 standard errors and SQL Server 2005 includes more than 6800 unique errors. All of these error numbers and messages are stored in the Master database.
This example is a simple stored procedure using a generic approach to error handling:
CREATE PROCEDURE spRunSQL @Statement VarChar(2000) AS DECLARE @StartTime DateTime , @EndTime DateTime , @ExecutionTime Int SET @StartTime = GetDate() EXECUTE (@Statement) IF @@Error = 0 BEGIN SET @EndTime = GetDate() SET @ExecutionTime = DateDiff(MilliSecond, @StartTime, @EndTime) RETURN @ExecutionTime END
Without the error-checking script, the remaining statements would be executed after the erroneous EXCUTE. . . line. The following example uses a more specific approach. This assumes that I want to replace the default error message and numbers with my own:
CREATE PROCEDURE spRunSQL @Statement VarChar(2000) -- Input param. accepts any SQL statement. AS DECLARE @StartTime DateTime , @EndTime DateTime , @ExecutionTime Int , @ErrNum Int SET @StartTime = GetDate() EXECUTE (@Statement) SET @ErrNum = @@Error IF @ErrNum = 207 -- Bad column RAISERROR (‘Bad column name’, ELSE IF @ErrNum = 208 -- Bad object RAISERROR (‘Bad object name’, ELSE IF @ErrNum = 0 -- No error. Resume. BEGIN SET @EndTime = GetDate() SET @ExecutionTime = DateDiff(MilliSecond, @StartTime, @EndTime) RETURN @ExecutionTime -- Return execution time in milliseconds END
EAN: 2147483647
Pages: 131