存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令，通俗来说，存储过程就是能完成一定操作的一组SQL语句，可视为批处理文件。
存储过程优势
- 存储过程只在创造时进行编译，以后每次执行存储过程无需再次编译。
- SQL语句每执行一次就编译一次，使用存储过程可提高数据库执行效率。
- 当对数据库进行复杂操作时，可将复杂的操作用存储过程封装起来与事务结合一起使用。
- 存储过程可重复使用，可减少数据库开发人员的工作量。
- 存储过程安全性高，仅对特定用户开放权限。
存储过程缺点
- 移植不便，存储过程依赖于数据库管理系统。
- 不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装。
- 代码可读性差，不移维护。
- 不支持集群
SQL Server的存储过程是使用T_SQL编写的代码段，目的在于能够方便的完成数据库操作。T_SQL是SQL Server与应用程序之间的编程接口。
存储过程分类
- 系统存储过程
系统存储过程是SQL Server自身提供的存储过程，可作为命令执行操作。主要用来从系统表中获取信息，以完成数据库服务器的管理工作。
系统存储过程位于数据库服务器中，以sp_开头。系统存储过程定位在系统定义master和用户定义的数据库中，调用时不必在存储过程前添加数据库限定名。
- 用户存储过程/自定义存储过程
自定义存储过程即用户使用T_SQL语句编写的，为实现某特定业务需求。自定义存储过程可接收输入参数、向客户端返回结果、返回输出参数等。
创建自定义存储过程时
存储过程前添加
##表示创建全局的临时存储过程存储过程前添加
#表示创建局部临时存储过程
局部临时存储过程只能在创建它的会话中使用，会话结束将被删除。两种存储过程都存放在tempdb数据库中。
用户定义的存储过程分为
- T_SQL
T_SQL存储过程是指保存的T_SQL语句集合，可接收和返回用户提供的参数，存储过程也可能从数据库向客户端应用程序返回数据。
- CLR
CLR 存储过程是指引用Microsoft .NET Framework公共语言的方法存储过程，可接收和返回用户提供的参数。
增删改查
创建存储过程
-- 创建无参数的存储过程
IF(EXISTS (SELECT * FROM sys.objects WHERE name="getUsers"))
DROP PROC prop_get_users
GO
CREATE PROCEDURE getUsers
AS
SELECT * FROM Uses;
-- 调用执行存储过程
EXEC getBooks;
-- 创建带参数的存储过程
IF(EXISTS (SELECT * FROM sys.objects WHERE name="searchUsers"))
DROP PROC proc_search_users
GO
CREATE PROC searchUsers(@UserID int)
AS
SELECT * FROM Users WHERE UserID=@UserID;
-- 执行存储过程
EXEC searchUsers 100;
-- 创建带多个参数的存储过程
IF(EXISTS(SELECT * FROM sys.objects WHERE name="searchUsers"))
DROP PROP proc_search_users
GO
CREATE PROC searchUsers(@UserID int, @UserName varchar(20))
AS
SELECT * FROM Users WHERE UserID=@UserID AND UserName=@UserName;
-- 执行存储过程
EXEC searchUsers 100, "admin";
-- 创建带返回值的存储过程
IF(EXISTS (SELECT * FROM sys.objects WHERE NAME = "getUserID") )
DROP PROC proc_get_userid
GO
CREATE PROC getUserID(@UserName varchar(20), @UserID int output)
AS
SELECT @UserID = UserID FROM Users WHERE UserName = @UserName;
-- 执行存储过程
DECLARE @id int -- 声明变量用于接收存储过程的返回值
EXEC getUserID "admin",@id output
SELECT @id AS UserID; -- AS 为返回的列起别名
修改存储过程
ALTER PROCEDURE dbo.getUsers
AS
SELECT auth FROM Users;
删除存储过程
DROP PROCEDURE getUsers;
重命名存储过程
sp_rename getUsers,proc_get_users
基础语法
-- 定义变量并赋值
declare @var int
set @var=1
print @var
-- 定义变量并使用select语句赋值
declare @var nvarchar(50)
select @var="superman"
print @var
declare @var nvarchar(50)
select @var = UserName from Users where UserID=1
print @var
-- 定义变量并使用update语句赋值
declare @var nvarchar(50)
update Users set @var=UserName where UserID=1
print @var
-- 创建临时表
create table #Tmp
{
[UserID] [int] NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](max) NULL,
}
实例：
编写存储过程，传入分号间隔的两个字符串，将对应的字符串提取后插入数据表对应的列中，若存在则更新。
keys = "01;02;03"
vals = "superman;antman,ironman"
实例：查询用户表中是否存在某账户的记录
CREATE PROC checkUser
@UserName varchar(50)
@Output varchar(8) output
AS
BEGIN
IF(SELECT COUNT(1) FROM Users AS u WHERE u.UserName=@UserName)>0
SET
@Output = "success"
ELSE
SET
@Output = "failure"
END
GO
DECLARE @output varchar(8)
EXEC checkUser "ironman",@output output
PRINT @output
