|
|
Now that you know how to create views, let’s move on to stored procedures. So the first question beckons: What is a stored procedure?
A stored procedure is a precompiled collection of Transact-SQL statements stored with the database and processed as a logical unit. SQL Server comes pre-loaded with its own sets of stored procedures used for managing the SQL Server and displaying information about databases and users. These supplied stored procedures are called system stored procedures. System stored procedures are used for database administrative tasks and are stored in the system database called Master. For example, you can use the sp_help system stored procedure to get information about objects in the current database.
| Note |
The maximum size of a stored procedure is 128MB. |
There are many advantages in putting T-SQL code in stored procedures rather than embedding it in a client application.
The stored procedure can be written once and stored with the database. This is often done by database experts and is a way of optimizing database operations. A stored procedure can be modified independently of the client application source code. Multiple client applications written in a variety of programming languages (e.g., VB .NET, C#, C++, ASP .NET, and Java) can use the same stored procedures. This helps avoid duplication of effort. It also allows customization of some aspects of the application without the need for change in the client’s source code.
Before any query can be executed by the server, the T-SQL codes it contains are parsed and an execution plan is created. The execution plan is an understanding of what the query wants to do, and it effectively guides the SQL Server to the most efficient way to execute the query.
When a stored procedure is created, an execution plan for the T-SQL code it contains is created, optimized, and stored with the procedure. Subsequently, when the stored procedure is executed, the same query plan is used. The stored procedure is also cached in memory after it is executed for the first time.
On the other hand, when a client application sends a query to the database, SQL Server parses the query to create an execution plan. The query is not cached, and the execution plan is recalculated each time the client sends the query. This makes stored procedures much more efficient, especially for long sets of T-SQL codes and repetitive codes such as those found in a loop.
An operation that requires hundred of lines of T-SQL statements can be achieved by executing just one line of code that calls a stored procedure. This reduces network traffic considerably, both in terms of what is sent to the server and also what is sent back to the client application. It also has the added bonus of reducing the amount of code in the client application, thus making it easier to maintain and follow.
A user can be given access to execute a given stored procedure, even if he does not have permission to execute a T-SQL statement found in the procedure. This way, you can allow users to update the data only through stored procedures where you can validate and implement business rules.
Stored procedures are created either within the Enterprise Manager using the Stored Procedure Properties window or in SQL Query Analyzer.
Click on the Stored Procedures node of the Northwind database in the console tree. All the stored procedures for Northwind are loaded inside the right-hand pane of the Enterprise Manager. Right-click the Stored Procedures node and click New Stored Procedure in the pop-up menu. The Stored Procedure Properties window opens. The Text field is where the logic for the procedure is coded. You can also edit existing stored procedures by double-clicking on the stored procedure name in the right pane. You cannot edit system stored procedures, but you can still see their code.
| Note |
Once you have opened the Stored Procedure Properties window, you must close it before you can use any other part of Enterprise Manager. |
You will find that working in SQL Query Analyzer is much easier. The Query Analyzer contains some useful tools that will help you with any T-SQL statements. For instance, you can see the execution plan that will warn of any bottleneck in the code.
| Note |
In SQL 2000, the Query Analyzer contains an object browser. |
| Note |
You can use the object browser to help code your stored procedures. You simply drag the object, which also includes T-SQL functions, to your programming window. The object browser can be toggled on and off from the menu by selecting Tools, Object Browser, Show/Hide. Alternatively, you can use F8 as a keyboard shortcut. |
The code for stored procedures is written in T-SQL. To effectively write complicated stored procedures, you must possess a firm knowledge of T-SQL.
Create a stored procedure:
CREATE PROC[EDURE] < procedure_name > [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH < procedure_attribute > [ ,...n ] ]
[ FOR REPLICATION ]
AS
< sql_statement > [ ...n ]
Modify a stored procedure:
ALTER PROC[EDURE] < procedure_name > [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH < procedure_attribute > [ ,...n ] ]
[ FOR REPLICATION ]
AS
< sql_statement > [ ...n ]
Delete a stored procedure:
DROP PROCEDURE < procedure_name > [ ,...n ]
| Note |
Arguments between [ ] are optional. Those between { } must appear together. |
< procedure_name >: The name of the stored procedure. The name can also include spaces, but if so, it must be enclosed in [ ]. Stored procedure names must follow the same conventions as identifiers.
; number: This is an optional number used to group procedures of the same name so that they can be deleted at the same time with a single DROP PROCEDURE statement. For example, you can create a set of procedures for samples in this book as sampleproc;1, sampleproc;2, and so on. After you have finished with the samples, you can delete the lot with one statement: DROP PROCEDURE sampleproc.
@parameter: This is the name of a parameter to the procedure. Parameter names must start with a @ and must follow the same conventions as identifiers. One or more parameters can be declared with a stored procedure. When the procedure is called, the value for each must be supplied, unless a default is defined. Stored procedures in SQL Server 2000 can have a maximum of 2,100 parameters.
Parameters are local to the procedure in which they are defined.
data_type: This is the parameter data type. You can use any MS SQL 2000 data types, including text, ntext, and image. The cursor data type can be used only on OUTPUT parameters. When you specify cursor parameters, the VARYING and OUTPUT keywords must also be specified.
| Note |
Cursor data types in parameters contain a reference to a cursor. In SQL, a cursor is a special object that works on result sets. Its functionality is similar to ADO .NET, and it allows row-by-row manipulation of the result set. Cursors are created using the CREATE CURSOR statement. |
VARYING: This applies only to cursor parameters and specifies that the result set is supported as an OUTPUT parameter. It denotes that the output is constructed dynamically by the stored procedure and its contents can vary.
Default: This is the default value for the parameter. The procedure can be executed without specifying a value (in which case the default value is assigned to the parameter). The default must be a constant or it can be null. It can also include strings with wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the Like keyword.
OUTPUT: This indicates that the parameter will return a value. The value can be returned to EXEC[UTE]. OUTPUT parameters are used to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. If the data type is a cursor, the OUTPUT option must be used.
,...n: n is a placeholder indicating more than one option can be specified.
< procedure_attribute >: Procedure attributes are RECOMPILE and ENCRYPTION.
RECOMPILE: Directs SQL Server to not cache a plan for this procedure, and the procedure is recompiled at run time. Use the RECOMPILE option when using temporary values without overriding the execution plan cached in memory.
ENCRYPTION: SQL Server stores the text for creating views in system table columns. This attribute option instructs SQL Server to encrypt the system table columns containing the text of the CREATE PROC[EDURE] statement. This prevents the stored procedure from being published as part of SQL Server replication. It also prevents database users from being able to see the code for the stored procedure.
FOR REPLICATION: Creates a stored procedure that is used as a stored procedure filter and is only executed during replication. This option cannot be used with the RECOMPILE option.
<sql_statement>: This is the T-SQL code that forms part of the procedure. This can also include calls to other stored procedures. The compiler will include all the SQL code in the script as part of the stored procedure. You can, however, indicate where the stored procedure ends by using the GO statement.
| Note |
The syntax for ALTER PROC[EDURE] is the same as for CREATE PROC[EDURE]. |
Stored procedures are usually called from the client; however, it is possible to issue a call to a stored procedure from within another stored procedure or trigger.
The syntax for executing a stored procedure is:
EXECUTE sp_name [@parameter = value] [, …n]
sp_name is the name of the stored procedure you want to execute. If the stored procedure takes any parameter, you should also pass a valid value to it. It is possible to pass the parameter values separated by commas. This is practical only when you know the order in which the parameters occur in the stored procedure. It is a better programming practice to include the @parameter = value syntax. This makes the code more readable.
The tool you will use most to create your stored procedure is the Query Analyzer. The new version has some templates included, which you can use as a building block for your stored procedure.
Now that you know how to create a stored procedure, it is time we look at an example.
| Note |
By convention, most stored procedure names are prefixed with sp and do not have spaces. However, you can use your own convention including spaces. |
In this example, our aim is to define a stored procedure that returns the customer ID and subtotal of an order. We will call the stored procedure sp_SubTotal.
| Note |
Stored procedures can only return an integer directly. Use this primarily to denote error codes. To return other types, use the OUTPUT parameter. Result set can also be returned if any SQL statement does so. |
 |
-- =============================================
-- Create procedure sp_SubTotal
-- =============================================
-- Drop the procedure if it already exists
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_SubTotal'
AND type = 'P')
DROP PROCEDURE sp_SubTotal
GO
-- creating the stored procedure version one
-- The values are returned as result sets
CREATE PROCEDURE sp_SubTotal;1
@OrderID integer
AS
SELECT Orders.CustomerID,
[Order Subtotals].Subtotal
FROM Orders INNER JOIN
[Order Subtotals] ON Orders.OrderID = [Order
Subtotals].OrderID
WHERE Orders.OrderID = @OrderID
GO
-- creating the stored procedure second version
-- The values are returned through OUPUT parameters
CREATE PROCEDURE sp_SubTotal;2
@OrderID integer,
@CustomerID nchar(5) OUTPUT,
@SubTotal money OUTPUT
AS
SELECT @CustomerID = Orders.CustomerID,
@SubTotal = [Order Subtotals].Subtotal
FROM Orders INNER JOIN
[Order Subtotals] ON Orders.OrderID = [Order
Subtotals].OrderID
WHERE Orders.OrderID = @OrderID
GO
-- =============================================
-- example to execute the stored procedure
-- =============================================
DECLARE @ID int
DECLARE @CustomerID nvarchar(5)
DECLARE @SubTotal money
SET @ID = 10249
-- Execute version 1, The default
EXECUTE sp_SubTotal @ID
-- Execute the second version
EXECUTE sp_SubTotal;2 @ID, @CustomerID OUTPUT, @SubTotal
OUTPUT
PRINT @CustomerID
PRINT CAST(@SubTotal as varchar)
GO
The script above produces two stored procedures that perform the same calculation. The difference is that version one returns a result set (same as if the query was run directly), and the second version returns values through OUTPUT parameters.
|
|
|