1.创建存储过程的基本语法模板：

if (exists (select * from sys.objects where name = 'pro_name'))
    drop proc pro_name
go
create proc pro_name
    @param_name param_type [=default_value]
as
begin    
    sql语句
end

ps:[]表示非必写内容。sys.objects存储的是本数据库中的信息，不仅仅存储表名，还有存储过程名 、视图名、触发器等等。

例如：

 1 if (exists (select * from sys.objects where name = 'USP_GetAllUser'))
 2     drop proc USP_GetAllUser
 3 go
 4 create proc USP_GetAllUser
 5 @UserId int =1
 6 as 
 7 set nocount on;
 8 begin
 9     select * from UserInfo where Id=@UserId
10 end

ps:SQL Server 实用工具将 GO 解释为应将当前的 Transact-SQL 批处理语句发送给 SQL Server 的信号。当前批处理语句是自上一 GO 命令后输入的所有语句，若是第一条 GO 命令，则是从特殊会话或脚本的开始处到这条 GO 命令之间的所有语句。

2.调用方法：

exec dbo.USP_GetAllUser 2;

ps:一般在执行存储过程是，最好加上架构名称，例如 dbo.USP_GetAllUser 这样可以可以减少不必要的系统开销，提高性能。 因为如果在存储过程名称前面没有加上架构名称，SQL SERVER 首先会从当前数据库sys schema（系统架构）开始查找，如果没有找到，则会去其它schema查找，最后在dbo架构（系统管理员架构）里面查找。

3.查看本数据库中存在的存储过程

依次展开数据库、可编程性、存储过程，即可看到已创建的存储过程。

4.修改存储过程

alter proc proc_name
as
　　sql语句

5.存储过程中的输出参数的使用

 1 if (exists(select * from  sys.objects where name='GetUser'))
 2     drop proc GetUser
 3 go 
 4 create proc GetUser
 5     @id int output,
 6     @name varchar(20) out
 7 as 
 8 begin 
 9     select @id=Id,@name=Name from UserInfo where Id=@id
10 end
11 s
12 go 
13 declare 
14 @name varchar(20),
15 @id int;
16 set @id=3;
17 exec dbo.GetUser @id,@name out;
18 select @id,@name;
19 print Cast(@id as varchar(10))+'-'+@name;

ps:参数output为该参数可以输出

6.分页获取数据的存储过程

 1 if (exists(select * from  sys.objects where name='GetUserByPage'))
 2     drop proc GetUserByPage
 3 go 
 4 create proc GetUserByPage
 5     @pageIndex int,
 6     @pageSize int
 7 as 
 8 declare 
 9 @startIndex int,
10 @endIndex int;
11 set @startIndex =  (@pageIndex-1)*@pageSize+1;
12 set @endIndex = @startIndex + @pageSize -1 ;
13 begin 
14     select Id,Name from 
15     (
16         select *,row_number()over (order by Id)as number from UserInfo  
17     )t where t.number>=@startIndex and t.number<=@endIndex
18 end
19 
20 go 
21 exec dbo.GetUserByPage 2,4;

7.存储过程中事务的创建

if (exists(select * from  sys.objects where name='JayJayToTest'))
    drop proc JayJayToTest
go 
create proc JayJayToTest
    @GiveMoney int,
    @UserName nvarchar(20)
as 
beginset nocount on;
    begin tran;
    begin try
        update BankTest set Money = Money-@GiveMoney where Name=@UserName;
        update BankTest set Money = Money+@GiveMoney where Name='test';
        commit;
    end try    
    begin catch        
        rollback tran;
        print ('发生异常，事务进行回滚');
    end catch    
end
go
exec JayJayToTest 10,'jayjay

8.了解存储过程的执行计划

SELECT * FROM sys.[syscacheobjects]查看当前缓存的执行计划

如果执行存储过程时成功通过解析阶段，则 Microsoft SQL Server 查询优化器将分析存储过程中的 Transact-SQL 语句并创建一个执行计划。执行计划描述执行存储过程的最快方法，所依据的信息包括：

1.表中的数据量。

2.表的索引的存在及特征，以及数据在索引列中的分布。

3.WHERE 子句条件所使用的比较运算符和比较值。

4.是否存在联接以及 UNION、GROUP BY 和 ORDER BY 关键字。

查询优化器在分析完存储过程中的这些因素后，将执行计划置于内存中。分析存储过程和创建执行计划的过程称为编译。优化的内存中的执行计划将用来执行该查询。执行计划将驻留在内存中，直到重新启动 SQL Server 或其他对象需要存储空间时为止。如果随后执行了存储过程，而现有执行计划仍留在内存中，则 SQL Server 将重用现有执行计划。如果执行计划不再位于内存中，则创建新的执行计划。

重新编译执行计划(create proc JayJayToTest with recompile)

创建存储过程时在其定义中指定 WITH RECOMPILE 选项，表明 SQL Server 将不对该存储过程计划进行高速缓存；该存储过程将在每次执行时都重新编译。当存储过程的参数值在各次执行间都有较大差异，导致每次均需创建不同的执行计划时，可使用 WITH RECOMPILE 选项。此选项并不常用，因为每次执行存储过程时都必须对其进行重新编译，这样会使存储过程的执行变慢。

由于数据库的新状态，数据库内的某些更改可能会导致执行计划效率低下或不再有效。SQL Server 检测这些使执行计划无效的更改，并将计划标记为无效。此后，必须为执行查询的下一个连接重新编译新的计划。导致计划无效的情况包括：

1.对查询所引用的表或视图进行任何结构更改（ALTER TABLE 和 ALTER VIEW）。
　　2.通过语句（如 UPDATE STATISTICS）显式生成或者自动生成新的分发内容统计。
　　3.除去执行计划所使用的索引。
　　4.显式调用 sp_recompile。
　　5.对键的大量更改（其他用户对由查询引用的表使用 INSERT 或 DELETE 语句所产生的修改）。
　　6.对于带触发器的表，inserted 或 deleted 表内的行数显著增长。

前言：

转载请附上连接,本帖原创请勿照抄。

存储过程用于完成特定功能的SQL语句集合，编译完后存储在数据库中，不同的程序在执行同一个SQL的时候可以直接调用存储过程无需重新写sql语句。

优点: 模块化程序、需只要调用无需传输SQL,加快SQL执行速度,提高安全性,减少流量,提高可移植性,维护方便。

缺点: 存储过程建立复杂的SQL业务进行过程化执行会导致执行速度变慢,需要应用缓存不太合适,不支持集群。

一.存储过程

存储过程格式(存储过程需要用到的SQL在最底部)

----重复建立存储过程会执行覆盖操作

create or replace procedure 过程名称 [定义参数(in获取参数 out输出参数)] 
----在视图中用AS 在游标中用IS
is或者as

----声明变量
begin

----执行代码

end;

举栗

----存储过程说明 
----select count(1) into inte from p_shool where p_a=inta;  根据传进来的inta查询shool的p_a字段，如果p_a有值 inte则=1，
----如果p_a没值 inte则=0
----if inte=1 then 判断inte=1 则p_a有名字则进行下一步的Insert into插入该名字拥有的存款
----最后 dbms_output.put_line 返回是否插入存款

----inta id   intb  money  intc ina+intb  ret 输出参数number类型(存储过程可以定义多个输出参数)
create or replace procedure csdn_test(inta in int,intb in int,intc out int,ret out number)
AS
inte int;
begin
   select count(1) into inte from p_shool where p_a=inta;
   if inte=1 then
    Insert into m_procedure values(inta,intb);
    dbms_output.put_line('输入money');
  else
  dbms_output.put_line('输入失败');
  end if;
end;

调用存储过程:

declare
----定义变量
begin
  ----调用存储过程
end;

举栗

declare
ret  number;
begin
  csdn_test(1,1000,0,ret);
end;

删除存储过程

drop procedure 过程名称

需要用到的SQL表

create table m_procedure
(
  m_inta       int,
  m_intb    int
)
select * from m_procedure;
create table p_shool
(
  p_a       int,
  p_b       varchar(5)
)
Insert into p_shool values(1,'小明');
Insert into p_shool values(2,'小黑');
Insert into p_shool values(3,'小白');

下一篇 Oracle存储函数(附带SQL语句完整过程及示例)

实现MFC连接存储过程并实现调用执行请参考下一篇博客

1. 掌握存储过程的概念、功能及使用方法；
2. 掌握执行存储过程的方法；

创建及管理存储过程

注意：以下所有实验基于该表✈点击前往查看

以实验数据库为基础数据，编写并执行以下存储过程
1.创建一个无参存储过程pr_stuscore，查询以下信息：学号、姓名、性别、课程名称、考试成绩。

CREATE PROC pr_stuscore
AS
   SELECT student.s_id,sname,ssex,cname,grade   from student,course,grade
   WHERE student .s_id=grade.s_id and course.c_id=grade.c_id
GO
EXEC pr_stuscore

2.创建一个带参数的存储过程pr_StuScoreInfo2，该存储过程根据传入的学生编号和课程名称查询以下信息：院系、学号、姓名、性别、课程名称、考试成绩。

CREATE PROCEDURE pr_StuScoreInfo2
@sid char(10),
@name char(4)
AS
   SELECT smajor,student.s_id,sname,ssex,cname,grade   from student,course,grade
   WHERE student .s_id=grade.s_id and course.c_id=grade.c_id and
   @sid=student.s_id and @name=cname
GO

3.创建一个带参数的存储过程pr_xxscore，该存储过程根据传入的学生编号和课程号，并通过变量输出该学生的课程成绩。

CREATE PROCEDURE pr_xxscore 
@sid char(10),
@cid char(4),
@grade char(3) OUTPUT
AS
   SELECT @grade=grade from student,course,grade
   WHERE student.s_id=grade.s_id and course.c_id=grade.c_id and
   @sid=student.s_id and @cid=grade.c_id

4.创建存储过程，通过给定两个学生的学号，比较两个学生的年龄，并将大的学生打印出来，并显示“**号学生年龄比较大”。

CREATE PROCEDURE pr_sbirthday
@si1 datetime OUTPUT,@si2 datetime OUTPUT,@sid1 char(10) ,@sid2 char(10)
as
select @si1=sbirthday from student where @sid1=s_id 
select @si2=sbirthday from student where @sid2=s_id 

IF   
  @si1>@si2
      PRINT @sid1+'号学生年龄比较大'
ELSE 
      PRINT @sid2+'号学生年龄比较大'

5.编写带参数的存储过程pr_cavg，根据传入的课程名称统计该课程的平均成绩。

CREATE PROCEDURE pr_cavg
@name nvarchar(10)
AS
select AVG(grade) from grade ,course,student where course.c_id=grade.c_id and
@name=cname

6.创建存储过程pr_year，用于统计某年出生的所有学生的学号、姓名及所选课程信息及成绩。

CREATE PROCEDURE pr_year
@year datetime
as
select student.s_id,sname,course.*,grade from student ,course,grade
where student .s_id=grade .s_id and course.c_id=grade.c_id
and
@year=sbirthday 

7.创建一个添加学生记录的存储过程stduentadd，用来给student表添加一条记录，
（2010190028’,‘徐小明’,‘男’,‘1989-12-30’,‘信息工程学院’）其他字段为NULL

CREATE PROCEDURE stduentadd
AS
INSERT INTO student
VALUES ('2010190028','徐小明','男','1989-12-30','信息工程学院', NULL ,NULL ,NULL, NULL  )

使用方法

1.打开“SQL Server Management Studio”窗口。
2.单击“标准”工具栏的“新建查询”按钮，打开“查询编辑器”窗口
3.在窗口中输入以下SQL查询命令并执行