MS SQL 的存储过程练习

/*带参存储过程
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 stu_id between @startId and @endId
go*/


/*调用存储过程
exec proc_find_stu 7, 9*/


--带通配符参数存储过程
/*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 stu_name like @name and stu_name like @nextName;
go*/


--执行存储过程
/*exec proc_findStudentByName;
exec proc_findStudentByName '%o%','t%';*/


--带输出参数存储过程
/*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 = stu_name, @age = stu_age from student where stu_id = @id and stu_age = @age;
go*/


--
/*declare @id int,
        @name varchar(20),
@temp varchar(20);
set @id = 9;
set @temp = 40;
exec proc_getStudentRecord @id,@name out,@temp output;
select @name, @temp
print @name + '#' + @temp;*/


--不缓存存储过程
--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;


--加密WITH ENCRYPTION
/*if (OBJECT_ID('proc_temp_encryption','P')is not null)
   drop proc proc_temp_ecryption
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';*/


--带游标参数存储过程
/*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 stu_id, stu_name, stu_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;--删除游标*/