Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.
Benefits of Stored Procedures
Why should you use stored procedures? Let’s take a look at the key benefits of this technology:
· Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
· Reduced client/server traffic. If network bandwidth is a concern in your environment, you’ll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
· Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you’ll find the development cycle takes less time.
· Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.
· They allow faster execution: If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are created, and an in-memory version of the procedure can be used after the procedure is executed the first time. Transact-SQL statements repeatedly sent from the client each time they run are compiled and optimized every time SQL Server executes them.
· Another benefit is that you can execute a stored procedure on either a local or remote SQL Server. This enables you to run processes on other machines and work with information across servers, not just local databases.
· An application program written in a language, such as C or Visual Basic, can also execute stored procedures, providing an optimum solution between the client-side software and SQL Server.
Definition:
Stored procedures are precompiled database queries that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to application developers for use in other environments, such as web applications.
Or
A stored procedure is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.
Or
Stored procedures are collections of SQL statements and control-of-flow language. Stored procedures differ from ordinary SQL statements and from batches of SQL statements in that they are pre-compiled. The first time you run a procedure, SQL Server’s query processor analyze it and prepares an execution plan that is ultimately stored in a system table. The subsequent execution of the procedure is according to the stored plan. Since most of the query processing work has already been performed, stored procedures execute almost instantaneously.
Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and/or a return code.
Structure:
Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you’ll find that stored procedures are actually quite simple.
Syntax
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
procedure_name
Is the name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique within the database and its owner. For more information, see Using Identifiers.
Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters. Specifying the procedure owner name is optional.
@parameter
Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined or the value is set to equal another parameter). A stored procedure can have a maximum of 2,100 parameters.
Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see EXECUTE.
data_type
Is the parameter data type. All data types, except the table data type, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server – supplied data types and their syntax, see Data Types.
Note: There is no limit on the maximum number of output parameters that can be of cursor data type.
default
Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.
OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.
Example:
Let’s take a look at a practical example. Assume we have the table shown at the bottom of this page, named Inventory. This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = ‘FL’
This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.
We can simplify this process through the use of a stored procedure. Let’s create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse.
Here’s the SQL code:
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
Our Florida warehouse manager can then access inventory levels by issuing the command
EXECUTE sp_GetInventory ‘FL’
The New York warehouse manager can use the same stored procedure to access that area’s inventory.
EXECUTE sp_GetInventory ‘NY’
Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Server creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.
Using parameters with procedures:
Stored procedures are very powerful but to be most effective the procedure must be somewhat dynamic, which enables you, the developer, to pass in values to be considered during the functioning of the stored procedure.
Here are some general guidelines for using parameters with stored procedures:
· You can define one or more parameters in a procedure.
· You use parameters as named storage locations just like you would use the parameters as variables in conventional programming languages, such as C and Visual Basic.
· You precede the name of a parameter with an at symbol (@) to designate it as a parameter.
· Parameter names are local to the procedure in which they’re defined.
You can use parameters to pass information into a procedure from the line that executes the parameter. You place the parameters after the name of the procedure on a command line, with commas to separate the list of parameters if there is more than one. You use system data types to define the type of information to be expected as a parameter.
In example below, the procedure is defined with three input parameters. The defined input parameters appear within the procedure in the position of values in the VALUE clause of an INSERT statement. When the procedure is executed, three literal values are passed into the INSERT statement within the procedure as a parameter list. A SELECT statement is executed after the stored procedure is executed to verify that a new row was added through the procedure.
Creating a Stored Procedure with Input Parameters
create procedure proc4 (@p1 char(15), @p2 char(20), @p3 int) as
insert into Workers
values (@p1, @p2, @p3)
go
proc4 `Brat’,Sales,3333
go
select * from Workers
where Badge=3333
Name Department Badge
——— ————— ———–
Brat Sales 3333
(1 row(s) affected).
Calling Stored Procedures from Your Application:
On the application side, it can be quite cumbersome to have to specify each value on every call to the stored procedure, even in cases where the value is NULL. In those cases, the calling application can use named arguments to pass information to SQL Server and the stored procedure. For example, if your stored procedure allows up to three different arguments, name, address, and phone, you can call the routine as follows:
exec sp_routine @name=”blah”
Displaying and Editing Procedures:
You use the system procedure sp_helptext to list the definition of a procedure, and sp_help to display control information about a procedure. The system procedures sp_helptext and sp_help are used to list information about other database objects, such as tables, rules, and defaults, as well as stored procedures.
Making Changes and Dropping Stored Procedures
Two closely related tasks that you’ll no doubt have to perform are making changes to existing stored procedures and removing no longer used stored procedures.
Changing an Existing Stored Procedure:
Stored procedures cannot be modified in place, so you’re forced to first drop the procedure, then create it again. Unfortunately, there is no ALTER statement that can be used to modify the contents of an existing procedure. This stems largely from the query plan that is created and from the fact that stored procedures are compiled after they are initiated.
Because the routines are compiled and the query plan relies on the compiled information, SQL Server uses a binary version of the stored procedure when it is executed. It would be difficult or impossible to convert from the binary representation of the stored procedure back to English to allow for edits. For this reason, it’s imperative that you maintain a copy of your stored procedures in a location other than SQL Server. Although SQL Server can produce the code that was used to create the stored procedure, you should always maintain a backup copy.
You can pull the text associated with a stored procedure by using the sp_helptext system stored procedure. The syntax of sp_helptext is as follows:
sp_helptext procedure_name
Removing Existing Stored Procedures:
You use the DROP PROCEDURE statement to drop a stored procedure that you’ve created. Multiple procedures can be dropped with a single DROP PROCEDURE statement by listing multiple procedures separated by commas after the keywords DROP PROCEDURE in the syntax:
DROP PROCEDURE procedure_name_1, …,procedure_name_n
Example of stored procedure:
Let’s assume that we have the following table named Inventory:
This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = ‘FL’
This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.
We can simplify this process through the use of a stored procedure. Let’s create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here’s the SQL code:
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
Our Florida warehouse manager can then access inventory levels by issuing the command:
EXECUTE sp_GetInventory ‘FL’
The New York warehouse manager can use the same stored procedure to access that area’s inventory:
EXECUTE sp_GetInventory ‘NY’
Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.
Source:
Books Online
SQLServerCentral
MSSQLTIPS
