存储过程

存储过程语法

create proc/procedure 存储过程名字

[

   {@参数数据类型} [默认值]  [output]         例：@city nvarchar(50)

]

as

     Sql语句

go

执行存储过程

Exec 存储过程名字

判断存储过程

if object_ID(N'存储过程名字‘，N'p’) is Not Null Drop procedure 存储过程名字

存储过程的具体运用

1.查询

创建不带参数的存储过程（例子：查询学生总数）

--查询存储过程IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL    DROP procedure PROC_SELECT_STUDENTS_COUNT;GOCREATE procedure PROC_SELECT_STUDENTS_COUNTAS     SELECT COUNT(ID) FROM StudentsGO
执行：EXEC PROC_SELECT_STUDENTS_COUNT

带参数的存储过程

--查询存储过程，根据城市查询总数IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_CITY_COUNT', N'P') IS NOT NULL    DROP procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT;GOCREATE procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT(@city nvarchar(50))AS    SELECT COUNT(ID) FROM Students WHERE City=@cityGO

执行：EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'

通配符，在参数值赋值时，加上相应的通配符

--3、查询姓氏为李的学生信息，含通配符

IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_SURNNAME', N'P') IS NOT NULL    DROP procedure PROC_SELECT_STUDENTS_BY_SURNNAME;GOCREATE procedure PROC_SELECT_STUDENTS_BY_SURNNAME    @surnName nvarchar(20)='李%' --默认值AS     SELECT ID,Name,Age FROM Students WHERE Name like @surnNameGO

执行：

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%'

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%

带有输出参数

--根据姓名查询的学生信息，返回学生的城市及年龄IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_NAME', N'P') IS NOT NULL    DROP procedure PROC_SELECT_STUDENTS_BY_NAME;GOCREATE procedure PROC_SELECT_STUDENTS_BY_NAME    @name nvarchar(50),     --输入参数    @city nvarchar(20) out, --输出参数    @age  int output        --输入输出参数AS     SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@ageGO

执行：

declare@namenvarchar(50),

@citynvarchar(20)，

@ageint;

set@name= N'李明';

set@age=20;

exec PROC_SELECT_STUDENTS_BY_NAME @name,@city out, @age output;

select@city,@age;

使用存储过程实现增删该

新增学生信息 

--1、存储过程：新增学生信息IF OBJECT_ID (N'PROC_INSERT_STUDENT', N'P') IS NOT NULL    DROP procedure PROC_INSERT_STUDENT;GOCREATE procedure PROC_INSERT_STUDENT    @id int,    @name nvarchar(20),    @age int,    @city nvarchar(20)AS     INSERT INTO Students(ID,Name,Age,City) VALUES(@id,@name,@age,@city)GO

执行

EXEC PROC_INSERT_STUDENT 1001,N'张三',19,'ShangHai'

根据学生ID，更新学生信息 

IF OBJECT_ID (N'PROC_UPDATE_STUDENT', N'P') IS NOT NULL    DROP procedure PROC_UPDATE_STUDENT;GOCREATE procedure PROC_UPDATE_STUDENT    @id int,    @name nvarchar(20),    @age int,    @city nvarchar(20)AS     UPDATE Students SET Name=@name,Age=@age,City=@city WHERE ID=@idGO

执行：

EXEC PROC_UPDATE_STUDENT 1001,N'张思',20,'ShangHai'

 根据ID，删除某学生记录

--3、存储过程：删除学生信息IF OBJECT_ID (N'PROC_DELETE_STUDENT_BY_ID', N'P') IS NOT NULL    DROP procedure PROC_DELETE_STUDENT_BY_ID;GOCREATE procedure PROC_DELETE_STUDENT_BY_ID    @id intAS     DELETE FROM  Students WHERE ID=@idGO

执行：EXEC PROC_DELETE_STUDENT_BY_ID1001

存储过程实现分页查询

使用row_number函数分页

--分页查询IF OBJECT_ID (N'PROC_SELECT_BY_PAGE', N'P') IS NOT NULL    DROP procedure PROC_SELECT_BY_PAGE;GOCREATE procedure PROC_SELECT_BY_PAGE    @startIndex int,    @endIndex intAS     SELECT  * FROM (SELECT ID,Name,Age,City,ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNumber FROM Students) AS Temp     WHERE Temp.RowNumber BETWEEN @startIndex AND @endIndexGO

执行：EXEC PROC_SELECT_BY_PAGE1,10

使用传统的top分页

--使用TOP分页IF OBJECT_ID (N'PROC_SELECT_BY_PAGE_WITH_TOP', N'P') IS NOT NULL    DROP procedure PROC_SELECT_BY_PAGE_WITH_TOP;GOCREATE procedure PROC_SELECT_BY_PAGE_WITH_TOP    @pageIndex int,    @pageSize intAS     SELECT TOP(@pageSize) * FROM Students     WHERE ID >=(SELECT MAX(ID) FROM (SELECT TOP(@pageSize*(@pageIndex-1) + 1) ID FROM Students ORDER BY ID) AS Temp)    GO

执行：EXEC PROC_SELECT_BY_PAGE_WITH_TOP1,2

其他功能

存储过程，每次执行都进行重新编译

--1、存储过程，重复编译IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_RECOMPILE', N'P') IS NOT NULL    DROP procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE;GOCREATE procedure PROC_SELECT_STUDENTS_WITH_RECOMPILEwith recompile --重复编译AS     SELECT * FROM StudentsGO

对存储过程进行加密，加密后，不能查看和修改源脚本

--2、查询存储过程，进行加密IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_ENCRYPTION', N'P') IS NOT NULL    DROP procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION;GOCREATE procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTIONwith encryption --加密AS     SELECT * FROM StudentsGO

执行：EXEC PROC_SELECT_STUDENTS_WITH_ENCRYPTION