SQL Server 创建一个存储过程
我们需要使用 CREATE PROCEDURE 语句创建一个存储过程，接着要补充存储过程的代码，如果存储过程将要接受参数，它们需要被包括在名称后，如下:
CREATE PROCEDURE myStoredProcedure AS ... OR CREATE PROCEDURE myStoredProcedure @{Parameter Name} {data type} AS ...
详细示例
下述代码创建了一个被称为 “LatestTasks” 的存储过程。
它接受一个参数名为 @Count. 当调用这个存储过程，通过 @count 参数，它决定你想要多少行返回。
代码如下：
CREATE PROCEDURE LatestTasks @Count int AS SET ROWCOUNT @Count SELECT TaskName AS LatestTasks, DateCreated FROM Tasks ORDER BY DateCreated DESC
SQL Server 执行存储过程
常用系统存储过程有：
exec sp_databases; --查看数据库 exec sp_tables; --查看表 exec sp_columns student;--查看列 exec sp_helpIndex student;--查看索引 exec sp_helpConstraint student;--约束 exec sp_stored_procedures; exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句 exec sp_rename student, stuInfo;--修改表、索引、列的名称 exec sp_renamedb myTempDB, myDB;--更改数据库名称 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库 exec sp_helpdb;--数据库帮助，查询数据库信息 exec sp_helpdb master;
系统存储过程示例：
--表重命名 exec sp_rename 'stu', 'stud'; select * from stud; --列重命名 exec sp_rename 'stud.name', 'sName', 'column'; exec sp_help 'stud'; --重命名索引 exec sp_rename N'student.idx_cid', N'idx_cidd', N'index'; exec sp_help 'student'; --查询所有存储过程 select * from sys.objects where type = 'P'; select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
用户自定义存储过程
1、 创建语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements2、 创建不带参数存储过程
--创建存储过程 if (exists (select * from sys.objects where name = 'proc_get_student')) drop proc proc_get_student go create proc proc_get_student as select * from student; --调用、执行存储过程 exec proc_get_student;
3、 修改存储过程
--修改存储过程 alter proc proc_get_student as select * from student;
4、 带参存储过程
--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;
5、 带通配符参数存储过程
--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';6、 带输出参数存储过程
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默认输入参数
@name varchar(20) out, --输出参数
@age varchar(20) output--输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;7、 不缓存存储过程
--WITH RECOMPILE 不缓存
if (object_id('proc_temp', 'P') is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from student;
go
exec proc_temp;8、 加密存储过程
--加密WITH ENCRYPTION
if (object_id('proc_temp_encryption', 'P') is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';9、 带游标参数存储过程
if (object_id('proc_cursor', 'P') is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
select id, name, age from student;
open @cur;
go
--调用
declare @exec_cur cursor;
declare @id int,
@name varchar(20),
@age int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id, @name, @age;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--删除游标10、 分页存储过程
---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
drop proc proc_cursor
go
create proc pro_page
@startIndex int,
@endIndex int
as
select count(*) from product
;
select * from (
select row_number() over(order by pid) as rowId, * from product
) temp
where temp.rowId between @startIndex and @endIndex
go
drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id('pro_page', 'P') is not null)
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over (order by id asc) as number from student
) t
where t.number between @startRow and @endRow;
exec pro_stu 2, 2;SQL Server 修改存储过程
如果需要修改现有的存储过程，只需更换掉 CREATE ，使用 ALTER。
我们在 “Latest” 和 “Tasks”间添加一个空格（即“Latest Tasks”），并添加描述字段，如下：
ALTER PROCEDURE LatestTasks @Count int AS SET ROWCOUNT @Count SELECT TaskName AS "Latest Tasks", Description, DateCreated FROM Tasks ORDER BY DateCreated DESC
SQL Server 系统存储过程
SQL Server 包含了大量的系统存储过程，以帮助数据库管理任务。
通过 GUI 执行的任务可以通过系统存储过程来完成。
例如，有些东西可以用系统存储过程的包括：
配置安全帐户
建立链接服务器
创建一个数据库维护计划
创建全文检索目录
添加远程登录
配置复制
设置调度作业
以及更多...
SQL Server 命名约定
一起来看看扩展系统存储过程节点，我们发现，他们的名字都以 sp_ 开始，这样的命名表明它是一个存储过程。 该系统存储过程显然遵循的命名约定，在存储过程制定一个一致的命名约定是好的，但是每个人的命名习惯都有不同。
有些人前缀的存储过程 usp_，另外其他人使用 SQL 关键字，如 SELECT，INSERT，UPDATE，DELETE；也有人使用的缩写是一些下划线（例如，latest_tasks）。
因此，我们的存储过程可以被命名为以下任意一种，这取决于命名约定的使用。
LatestTasks
latest_tasks
uspLatestTasks
usp_latest_tasks
selectLatestTasks
select_LatestTasks
select_latest_tasks
getLatestTasks
get_latest_tasks
不管选择哪一种，都要保持一致性，这样才会在需要使用存储过程时显得更加容易使用。
所以这是存储过程覆盖。