下面介紹十大不同類型存儲過程。

用戶自定義存儲過程    1、 創建語法 create PRoc | procedure pro_name    [{@參數數據類型} [=默認值] [output],     {@參數數據類型} [=默認值] [output],     ....    ]as    SQL_statements     2、 創建不帶參數存儲過程 --創建存儲過程if (exists (select * from sys.objects where name = 'proc_get_student'))    drop proc proc_get_studentgocreate proc proc_get_studentas    select * from student;--調用、執行存儲過程exec proc_get_student;   3、 修改存儲過程 --修改存儲過程alter proc proc_get_studentasselect * from student;   4、 帶參存儲過程 --帶參存儲過程if (object_id('proc_find_stu', 'P') is not null)    drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as    select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;   5、 帶通配符參數存儲過程 --帶通配符參數存儲過程if (object_id('proc_findStudentByName', 'P') is not null)    drop proc proc_findStudentByNamegocreate proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')as    select * from student where name like @name and name like @nextName;goexec proc_findStudentByName;exec proc_findStudentByName '%o%', 't%';   6、 帶輸出參數存儲過程 if (object_id('proc_getStudentRecord', 'P') is not null)    drop proc proc_getStudentRecordgocreate 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_tempgocreate proc proc_tempwith recompileas    select * from student;goexec proc_temp;   8、 加密存儲過程 --加密WITH ENCRYPTION if (object_id('proc_temp_encryption', 'P') is not null)    drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionas    select * from student;goexec 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_cursorgocreate proc proc_cursor    @cur cursor varying outputas    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);endclose @exec_cur;deallocate @exec_cur;--刪除游標   10、 分頁存儲過程 ---存儲過程、row_number完成分頁if (object_id('pro_page', 'P') is not null)    drop proc proc_cursorgocreate proc pro_page    @startIndex int,    @endIndex intas    select count(*) from product;        select * from (        select row_number() over(order by pid) as rowId, * from product     ) temp    where temp.rowId between @startIndex and @endIndexgo--drop proc pro_pageexec pro_page 1, 4----分頁存儲過程if (object_id('pro_page', 'P') is not null)    drop proc pro_stugocreate 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;