SQL Server 2012 存储过程

1.存储过程

存储过程是由过程化SQL语句书写的过程，这个过程经过编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。使用存储过程可以实现代码的复用、提高运行效率、降低通信流量以及方便实施业务规则。
1.系统存储过程：
SQL Server内置存储过程，物理上存储在Resource中，逻辑上存储在各个数据库的sys架构中。系统存储过程以“sp_”开头。
2.用户存储过程：
用户自定义的存储过程，可以在用户数据库或除了Resource之外的其他系统数据库中创建。
3.临时存储过程：
临时存储过程存储在tempdb临时数据库中，以“#”开头的临时存储过程称为局部临时存储过程，创建后仅对当前用户连接可见，当用户关闭连接时被删除；以“##”开头的临时存储过程称为全局临时存储过程，创建后对任何用户都可见，当最后一个用户会话结束时被删除。

2.无参存储过程

语法格式：

--创建存储过程
CREATE PROC <过程名>
AS
<过程化SQL块>

--使用存储过程
EXEC <过程名>

使用示例：创建getMaleStudent存储过程返回student表中的男同学信息

--创建存储过程
create proc getMaleStudent
as
select *
from student
where Sex='男'

--使用存储过程
exec getMaleStudent

3.带入参存储过程

语法格式：

--创建存储过程
CREATE PROC <过程名>(<入参1> [,<入参2>]...)
AS
<过程化SQL块>

--使用存储过程
EXEC <过程名> <入参值> [,<入参值>]...

使用示例：创建getStudentBySex存储过程根据性别参数返回对应的学生信息

--创建存储过程
create proc getStudentBySex(@Sex nvarchar(5))
as
select *
from student
where Sex=@Sex

--使用存储过程
exec getStudentBySex '男'

4.带出参存储过程

语法格式：

--创建存储过程
CREATE PROC <过程名>(<入参1>,<出参1 OUTPUT> [,<入参或出参>]...)
AS
<过程化SQL块>

--使用存储过程
EXEC <过程名> <入参值>,<出参 OUTPUT> [,<入参值或出参>]...

使用示例：创建getStudentNum存储过程查看不同性别的学生数量

--创建存储过程
create proc getStudentNum(@Sex nvarchar(5),@StuNum int output)
as
select @StuNum=count(*)
from student
where Sex=@Sex

--使用存储过程
declare @StuNum int  --定义变量
declare @Sex nvarchar(5) ='男'
exec getStudentNum @Sex ,@StuNum output
select @sex 性别,@StuNum 数量

5.修改存储过程

1. 使用SSMS工具
   依次打开数据库，选择“可编程性”，然后选择“存储过程”，右击需要修改的存储过程，选择“修改”，进入查询编辑器，在编辑器中对存储过程进行修改。
   
   点击执行完成存储过程的修改
   
2. 使用SQL方式
   语法格式：

--修改存储过程
ALTER PROC <过程名>[(<入参1>,<出参1 OUTPUT> [,<入参或出参>]...)]
AS
<过程化SQL块>

使用示例：修改getMaleStudent存储过程查看女学生信息

--修改存储过程
alter proc getMaleStudent
as
select *
from student
where Sex='女'

6.查看存储过程

语法格式：

--查看存储过程属性信息、参数与数据类型
sp_help <存储过程名>

使用示例：使用sp_help查看getStudentNum存储过程

sp_help getStudentNum

语法格式：

--查看存储过程源代码
sp_helptext <存储过程名>

使用示例：使用sp_helptext查看getStudentNum存储过程

sp_helptext getStudentNum

7.删除存储过程

1. 使用SSMS工具删除存储过程
   依次打开数据库，选择“可编程性”，然后选择“存储过程”，右击需要修改的存储过程，选择“删除”
   
   进入删除对象对话框，点击确定即可完成存储过程的删除。
   

2. 使用SQL方式删除存储过程
   语法格式：

DROP PROC <存储过程名>

使用示例：删除getStudentBySex存储过程

drop proc getStudentBySex