MSSQL储存过程（转）
1、系统自带储存过程。
1 exec sp_databases; --查看数据库 2 exec sp_tables; --查看表 3 exec sp_columns student;--查看列 4 exec sp_helpIndex student;--查看索引 5 exec sp_helpConstraint student;--约束 6 exec sp_stored_procedures; 7 exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句 8 exec sp_rename student, stuInfo;--修改表、索引、列的名称 9 exec sp_renamedb myTempDB, myDB;--更改数据库名称 10 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库 11 exec sp_helpdb;--数据库帮助，查询数据库信息 12 exec sp_helpdb master;
示例：
1 --表重命名 2 exec sp_rename 'stu', 'stud'; 3 select * from stud; 4 --列重命名 5 exec sp_rename 'stud.name', 'sName', 'column'; 6 exec sp_help 'stud'; 7 --重命名索引 8 exec sp_rename N'student.idx_cid', N'idx_cidd', N'index'; 9 exec sp_help 'student'; 10 11 --查询所有存储过程 12 select * from sys.objects where type = 'P'; 13 select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
2、用户自定义存储过程。
（1）语法。
1 create proc | procedure pro_name 2 [{@参数数据类型} [=默认值] [output], 3 {@参数数据类型} [=默认值] [output], 4 .... 5 ] 6 as 7 SQL_statements
（2）创建不带参数存储过程。
1 if (exists (select * from sys.objects where name = 'proc_get_student')) 2 drop proc proc_get_student 3 go 4 create proc proc_get_student 5 as 6 select * from student; 7 8 --调用、执行存储过程 9 exec proc_get_student;
（3） 修改存储过程。
1 alter proc proc_get_student 2 as 3 select * from student;
（4）带参存储过程。
1 if (object_id('proc_find_stu', 'P') is not null) 2 drop proc proc_find_stu 3 go 4 create proc proc_find_stu(@startId int, @endId int) 5 as 6 select * from student where id between @startId and @endId 7 go 8 9 exec proc_find_stu 2, 4;
（5）带通配符参数存储过程。
1 if (object_id('proc_findStudentByName', 'P') is not null) 2 drop proc proc_findStudentByName 3 go 4 create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%') 5 as 6 select * from student where name like @name and name like @nextName; 7 go 8 9 exec proc_findStudentByName; 10 exec proc_findStudentByName '%o%', 't%';
（6）带输出参数存储过程。
1 if (object_id('proc_getStudentRecord', 'P') is not null) 2 drop proc proc_getStudentRecord 3 go 4 create proc proc_getStudentRecord( 5 @id int, --默认输入参数 6 @name varchar(20) out, --输出参数 7 @age varchar(20) output--输入输出参数 8 ) 9 as 10 select @name = name, @age = age from student where id = @id and sex = @age; 11 go 12 13 -- 14 declare @id int, 15 @name varchar(20), 16 @temp varchar(20); 17 set @id = 7; 18 set @temp = 1; 19 exec proc_getStudentRecord @id, @name out, @temp output; 20 select @name, @temp; 21 print @name + '#' + @temp;
（7）不缓存存储过程。
1 --WITH RECOMPILE 不缓存 2 if (object_id('proc_temp', 'P') is not null) 3 drop proc proc_temp 4 go 5 create proc proc_temp 6 with recompile 7 as 8 select * from student; 9 go 10 11 exec proc_temp;
（8）加密存储过程。
1 if (object_id('proc_temp_encryption', 'P') is not null) 2 drop proc proc_temp_encryption 3 go 4 create proc proc_temp_encryption 5 with encryption 6 as 7 select * from student; 8 go 9 10 exec proc_temp_encryption; 11 exec sp_helptext 'proc_temp'; 12 exec sp_helptext 'proc_temp_encryption';
（9）带游标参数存储过程。
1 if (object_id('proc_cursor', 'P') is not null) 2 drop proc proc_cursor 3 go 4 create proc proc_cursor 5 @cur cursor varying output 6 as 7 set @cur = cursor forward_only static for 8 select id, name, age from student; 9 open @cur; 10 go 11 --调用 12 declare @exec_cur cursor; 13 declare @id int, 14 @name varchar(20), 15 @age int; 16 exec proc_cursor @cur = @exec_cur output;--调用存储过程 17 fetch next from @exec_cur into @id, @name, @age; 18 while (@@fetch_status = 0) 19 begin 20 fetch next from @exec_cur into @id, @name, @age; 21 print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age); 22 end 23 close @exec_cur; 24 deallocate @exec_cur;--删除游标
（10）分页存储过程。
1 if (object_id('pro_page', 'P') is not null) 2 drop proc proc_cursor 3 go 4 create proc pro_page 5 @startIndex int, 6 @endIndex int 7 as 8 select count(*) from product 9 ; 10 select * from ( 11 select row_number() over(order by pid) as rowId, * from product 12 ) temp 13 where temp.rowId between @startIndex and @endIndex 14 go 15 --drop proc pro_page 16 exec pro_page 1, 4 17 -- 18 --分页存储过程 19 if (object_id('pro_page', 'P') is not null) 20 drop proc pro_stu 21 go 22 create procedure pro_stu( 23 @pageIndex int, 24 @pageSize int 25 ) 26 as 27 declare @startRow int, @endRow int 28 set @startRow = (@pageIndex - 1) * @pageSize +1 29 set @endRow = @startRow + @pageSize -1 30 select * from ( 31 select *, row_number() over (order by id asc) as number from student 32 ) t 33 where t.number between @startRow and @endRow; 34 35 exec pro_stu 2, 2;
