Home arrow SQL Server arrow Page 2 - SQL Server Stored Procedures 101
SQL SERVER

SQL Server Stored Procedures 101


Stored procedures can offer performance gains when used instead of regular queries. In this article Himanshu starts with the basics and gives us the "101" on stored procedures.

Author Info:
By: A.P.Rajshekhar
Rating: 4 stars4 stars4 stars4 stars4 stars / 178
June 03, 2002
TABLE OF CONTENTS:
  1. · SQL Server Stored Procedures 101
  2. · What are stored procedures?
  3. · Using parameters with procedures
  4. · Conclusion

print this article
SEARCH DEVARTICLES

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
  • 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.
blog comments powered by Disqus
SQL SERVER ARTICLES

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials