Stored Procedure| Database Stored Procedures| SQL Stored Procedures

A stored procedure is a set of SQL statements that can be stored in the server.

Benefits of Stored Procedures
1. 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.

2. 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.

3. 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.

4. Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Syntax:
CREATE PROCEDURE [procedure name] (@parameter1 type, @parameter2 type) AS
Sql statements………
BEGIN
END
…………….
GO

To declare variables:
DECLARE
Eg: DECLARE @temp int

To assign values:
SET
Eg: SET @temp = 0

To return a value:
CREATE PROCEDURE [proc name] (@p1 type, @p2 type output) AS
……
GO
Eg: Create Procedure GetAuthorsByLastName1 (@au_lname varchar(40), @RowCount int output) as select * from authors where au_lname like @au_lname; /* @@ROWCOUNT returns the number of rows that are affected by the last statement. */
select @RowCount=@@ROWCOUNT

Error Handling:
@@Error is used.

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

To Execute in query analyzer:
Exec [procedure name] [parameters]

To call from ASP page:
Rs.Open "exec [procedure name] [parameters]", conn

To write comments:
/* */

Tags: Stored Procedure, Database Stored Procedures, SQL Stored Procedures, Stored Procedure Syntax, Call a stored procedure from asp page, Execute a stored procedure, Error Handling in stored procedures