Introduction to SQL Server stored procedures

A stored procedure is a group of statements consisting of one or more T-SQL statements that can accept input and output parameters and can return multiple values to the calling program, can call other procedures, and can return a result status value to the calling program to mark execution success or failure;

Benefits of using Stored procedures

1. Reduce server/Client network traffic: Process multiple commands for a single batch execution, only one network transmission;
2. Stronger security: To prevent SQL injection, but also to encrypt processing;
3. Code reuse: Can encapsulate duplicate database operations;
4. Easier maintenance: Updating stored procedure scripts does not update the called server-side programs;
5. Better performance: The execution plan is generated for the first execution and is used for subsequent reuse;

Create a stored procedure

The stored procedure creation syntax is as follows:

USE Database;GOCREATE PROCEDURE getStudents    @num int,    @name  varchar(32)AS    DECLARE @condi varchar(32);    SET　@condi = @name + ‘%‘;    select top (@num) * from student        where name like @condi;GO

The stored procedure above has two parameters, indicating that the query begins with the first @num record in name @name, such as the incoming @num=10, @name = ' Li ', then returns the first 10 student records whose names begin with ' Li '.

Executing stored procedures

If the stored procedure does not take parameters: exec proc_name;

If the stored procedure takes parameters, it is passed to the parameter by method signature, as above example: exec getstudents 10, ' Li ';

To delete a stored procedure

Deleting a stored procedure uses the Drop keyword just as you would delete a database or table. such as: DROP PROCEDURE getstudents;

modifying stored procedures

Modifying a stored procedure can either use the ALTER keyword, or you can now delete the original stored procedure and then create the new. Such as:

(1) Use ALTER:

ALTER PROCEDURE getStudentsAS     select * from student;GO

(2) Delete before creating:

IF OBJECT_ID(‘getStudents‘, ‘P‘) IS NOT NULL    DROP PROCEDURE getStudents;GOCREATE PROCEDURE getStudentsAS    select * from student;GO

The effect is the same in both of these ways.

Returning data from stored procedures

The OUTPUT keyword is used in the stored procedure to return data. See Example:

IF OBJECT_ID(‘getStudentName‘, ‘P‘) IS　NOT NULL    DROP PROCEDURE getStudentName;GOCREATE PROCEDURE getStudentName    @name nvarchar(32) OUTPUTAS    select @name=name from student where name=‘ray‘;RETURNGO

The change process returns the name of student to the @name variable, called as follows:

DECLARE @RES nvarchar(32);EXEC getStudentName @[email protected] OUTPUT;PRINT @res;

Sometimes, the stored procedure output parameters are often used in conjunction with the database cursor (cursor), which you can refer to for usage.

Resources

• Msdn

Introduction to SQL Server stored procedures