如何在sqlserver中写存储过程

original link http://www.codeproject.com/Articles/126898/Sql-Server-How-to-write-a-Stored-procedure-in-Sql

存储过程在sqlserver 中被定义成一组有逻辑关系的sql语句 ，他们实现了一些特定的任务。使用存储过程有很多好处。最主要的优点是提高数据库性能。

还有如下好处

1.   减少了送达数据库的信息量，这在网络带宽有限的情况下是很重要的 。如果将一个有循环的sql执行语句通过网络到数据库执行。执行没有返回之前网络连接断掉或者rollback语句没有得到执行都会产生问题
2.   存储过程创建好之后只要编译一次。但sql语句每次送达执行时都需要编译一次尽管每次送达执行的sql语句都一样
3.   提高了sql代码的复用性，因为在多个用户端可以调用同一份处理过程避免每次书写相同的sql语句。也减少了开发的时间
4.   有助于提高安全性，我们可以给指定的用户授予某个存储过程的权限从而避免把存储过程中涉及的表的权限的授予
5.   有时候用存储过程来封装商业逻辑更安全，如果需要变化只在server端即可更改不需要对web端做任何改动

假设有一张表叫tbl_Students 结构如下：

CREATE TABLE  tbl_Students

(
    [Studentid] [int] IDENTITY(1,1) NOT NULL,
    [Firstname] [nvarchar](200) NOT  NULL,
    [Lastname] [nvarchar](200)  NULL,
    [Email] [nvarchar](100)  NULL
)

我们添加以下数据到上表

insert into tbl_Students n(Firstname.Lastname,email)  

 values('Vivek', 'Johari', 'vivek@abc.com')Insert into tbl_Students (Firstname, lastname, Email) Values('Pankaj', 'Kumar', 'pankaj@abc.com')Insert into tbl_Students (Firstname, lastname, Email) Values('Amit', 'Singh', 'amit@abc.com')Insert into tbl_Students (Firstname, lastname, Email) Values('Manish', 'Kumar', 'manish@abc.comm')

Insert into tbl_Students (Firstname, lastname, Email) Values('Abhishek', 'Singh', 'abhishek@abc.com')

现在我们开始写存储过程，第一步是创建存储过程语句

Create Procedure Procedure-name 
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
     Sql statement used in the stored procedure
End

我们来创建一个存储过程以studentid 为参数 返回studentname  内容如下

/*  Getstudentname is the name of the stored procedure*/Create  PROCEDURE Getstudentname(@studentid INT                   --Input parameter ,  Studentid of the student )ASBEGINSELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid END

 我们还可以把student name 作为output 参数 传出  代码如下

 /* 
GetstudentnameInOutputVariable is the name of the stored procedure which
uses output variable @Studentname to collect the student name returns by the
stored procedure
*/

Create  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                       --Input parameter ,  Studentid of the student
@studentname VARCHAR(200)  OUT        -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

在sqlserver 中 可以通过修改关键字来修改其内容。 假如我们想加入email address 信息在上述存储过程中，就得再引入一个output 参数 如下

/* 
Stored Procedure GetstudentnameInOutputVariable is modified to collect the
email address of the student with the help of the Alert Keyword
*/

 

Alter  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                   --Input parameter ,  Studentid of the student
@studentname VARCHAR (200) OUT,    -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT     -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname, 
    @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END

注意 并不是所有的存储过程都需要写入返回值 ，例如一些插入 删除 更新的语句如下

/*
This Stored procedure is used to Insert value into the table tbl_students. 
*/

Create Procedure InsertStudentrecord
(
 @StudentFirstName Varchar(200),
 @StudentLastName  Varchar(200),
 @StudentEmail     Varchar(50)
) 
As
 Begin
   Insert into tbl_Students (Firstname, lastname, Email)
   Values(@StudentFirstName, @StudentLastName,@StudentEmail)
 End

Sqlserver中执行存储过程

  sqlserver中的存储过程可以用execute 或exec 来执行。例如执行存储过程Getstudentname

Execute Getstudentname 1Exec Getstudentname 1

  执行，带有output参数的存储过程，首先我们要声明变量来容纳参数，例如

Declare @Studentname as nvarchar(200)   -- Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50)     -- Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail      -- "Select" Statement is used to show the output from Procedure

结 束语

   我们可以说储过程不但可以增强代码的可复用执行，还可以通过减少网络数据来提高数据库性能！