SQL Server Stored Procedures 101 - What are stored procedures?
(Page 2 of 4 )
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 analyzes 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.
Stored procedures in SQL Server are similar to procedures in other programming languages in that they can:
- Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- Contain programming statements that perform operations in the database, including calling other procedures.
- Return a status value to a calling procedure or batch to indicate success or failure (and the reason for the failure).
Benefits- Precompiled: 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: Multiple users and client programs can use stored procedures. 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.
Defining Stored Procedures You use the CREATE PROC[EDURE] statement to create a stored procedure. The maximum stored procedure name length is thirty characters. The syntax that you use to define a new procedure is as follows:
CREATE PROCEDURE [owner,] procedure_name [;number]
[@parameter_name datatype [=default] [OUTput]
...
[@parameter_name datatype [=default] [OUTput]
[FOR REPLICATION] | [WITH RECOMPILE] , ENCRYPTION
AS sql_statements In this example, a simple procedure is created that contains a SELECT statement to display all rows of a table. After the procedure is created, its name is entered on a line to execute the procedure.
Stored procedures can either be created by sending commands to SQL Server through ADO, or they can be created in the Query Analyzer application, which is the most popular way to do so.
Creating and Running a Stored Procedure create procedure all_employees
as select * from employees
go
exec all_employees name department badge
-------------------- -------------------- -----------
Brat Smith Sales 1234
Karen Jones Sales 5514
( 2 row(s) affected)
When you submit a stored procedure to the system, SQL Server compiles and verifies the routines within it. If any problems are found, the procedure is rejected and you'll need to determine what the problem is prior to re-submitting the routine. If your stored procedure references another, as yet unimplemented stored procedure, you'll receive a warning message, but the routine will still be installed.
If you leave the system with the stored procedure that you previously referred to uninstalled, then the user will receive an error message at runtime.
Stored procedures are treated like all other objects in the database. They are therefore subject to all of the same naming conventions and other limitations. For example, the name of a stored procedure cannot contain spaces, and it can be accessed using the database <object> convention.
Next: Using parameters with procedures >>
More SQL Server Articles
More By Himanshu Khatri
/ 125 
