存储过程和事务2

    dropprocproc_find_stu
go
createprocproc_find_stu(@startIdint, @endIdint)
as
   select*fromstudentwhereidbetween@startIdand@endId
go

execproc_find_stu 2, 4;

   5、带通配符参数存储过程

--带通配符参数存储过程
if(object_id('proc_findStudentByName','P')isnotnull)
   dropprocproc_findStudentByName
go
createprocproc_findStudentByName(@namevarchar(20) ='%j%', @nextNamevarchar(20) ='%')
as
   select*fromstudentwherenamelike@nameandnamelike@nextName;
go

execproc_findStudentByName;
execproc_findStudentByName'%o%','t%';

   6、带输出参数存储过程

if(object_id('proc_getStudentRecord','P')isnotnull)
   dropprocproc_getStudentRecord
go
createprocproc_getStudentRecord(
    @idint, --默认输入参数
    @namevarchar(20)out, --输出参数
    @agevarchar(20)output--输入输出参数
)
as
   select@name = name, @age = age fromstudentwhereid = @idandsex = @age;
go

--
declare@idint,
        @namevarchar(20),
        @tempvarchar(20);
set@id = 7;
set@temp = 1;
execproc_getStudentRecord @id, @nameout, @tempoutput;
select@name, @temp;
print@name +'#'+ @temp;

   7、不缓存存储过程

--WITHRECOMPILE不缓存
if(object_id('proc_temp','P')isnotnull)
   dropprocproc_temp
go
createprocproc_temp
withrecompile
as
   select*fromstudent;
go

execproc_temp;

   8、加密存储过程

--加密WITH ENCRYPTION
if(object_id('proc_temp_encryption','P')isnotnull)
   dropprocproc_temp_encryption
go
createprocproc_temp_encryption
withencryption
as
   select*fromstudent;
go

execproc_temp_encryption;
execsp_helptext'proc_temp';
execsp_helptext'proc_temp_encryption';

   9、带游标参数存储过程

if(object_id('proc_cursor','P')isnotnull)
   dropprocproc_cursor
go
createprocproc_cursor
    @curcursorvaryingoutput
as
   set@cur =cursorforward_onlystaticfor
   selectid, name, agefromstudent;
   open@cur;
go
--调用
declare@exec_curcursor;
declare@idint,
        @namevarchar(20),
        @ageint;
execproc_cursor @cur = @exec_curoutput;--调用存储过程
fetchnextfrom@exec_curinto@id, @name, @age;
while(@@fetch_status = 0)
begin
   fetchnextfrom@exec_curinto@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')isnotnull)
   dropprocproc_cursor
go
createprocpro_page
    @startIndexint,
    @endIndexint
as
   selectcount(*)fromproduct
;   
   select*from(
       selectrow_number()over(orderbypid)asrowId, *fromproduct
    ) temp
   wheretemp.rowIdbetween@startIndexand@endIndex
go
--dropprocpro_page
execpro_page 1, 4
--
--分页存储过程
if(object_id('pro_page','P')isnotnull)
   dropprocpro_stu
go
createprocedurepro_stu(
    @pageIndexint,
    @pageSizeint
)