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;--删除游标*/