1 use MySchool  2 go  3   4 alter proc P_stu  5 @pass int= 60  6 as  7     select AVG(StudentResult)as 平均分  8     from Result  9  10     select * from Result 11     where StudentResult< @pass 12     go 13  14      15     exec P_stu 16  17     go 18  19 ---创建带输出参数的存储过程 20  21 select * from Result 22 go  23  24 alter procedure p_res  25 @newId varchar(20) output 26 as 27     declare @max varchar(20) 28      29     select @max=MAX(StudentNo) from Result 30     where YEAR(ExamDate)  = YEAR(GETDATE()) 31     and month(ExamDate)  = month(GETDATE()) 32     and day(ExamDate)  = day(GETDATE()) 33  34     if (@max is null)  35     set @newId = CONVERT(varchar(100),GETDATE(),23)+'001' 36     else 37     begin  38         declare @right int 39         set @right = CONVERT(int,right(@max,3)) 40         set @right = @right +1 41         set @newId = CONVERT(varchar(100),GETDATE(),23)+'-'+(case when @right < 10 then '00' when @right<100 then '0' end)+ CONVERT(varchar(10),@right) 42  43     end 44     go 45  46     declare @rst varchar(20) 47     exec p_res  @rst output 48     print @rst  49      50  51     --动态存储过程的数据处理 52  53  54     --分页存储过程 55     --要求 56         --可输入每页显示条数，默认每页条 57         --可输入页码进行查询 58         --可输入条件查询 59         --可输入表名 60         --可输入列名 61         --可输入排序列 62         --返回总记录数 63         --返回总页数 64         --返回查询到的结果 65   select * from Subject 66   go  67  68   if exists(select * from sysobjects  69     where name = 'p_Mypage') 70     drop procedure p_Mypage 71     go 72  73   create proc p_Mypage 74   @tableName varchar(20), 75   @Fields varchar(200), 76   @orderFields varchar(200), 77   @where varchar(200), 78   @pageSize int = 5, 79   @pageIndex int = 1, 80   @RecordCount int output, 81   @PageCount int output 82  83   with encryption--文本加密 84   --with recomple--每次都重新编译 85   as 86  87     declare @sql nvarchar(4000) 88  89     set @sql = 'select @RecordCount=count(*) from'+ @tableName+'where 1= 1'+ISNULL(@where,' ') 90  91     exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output 92     set @PageCount = ceiling((@RecordCount+0.0)/@pageSize)、 93      94     set @sql = ' 95     select * from( 96     select top  '+ CONVERT(varchar(10),@pageSize)+' '+@Fields+' from( 97     select top '+ CONVERT(varchar(10),@pageSize*@pageIndex)+' * from '+@tableName+' 98     where 1= 1'+ISNULL(@where,' ')+' 99     order by '+@orderFields+' )as a100     order by a.'+@orderFields+' desc) as b101     order by b.SubjectNo'102 103     exec (@sql)104 105 106     declare @a int ,@b int 107     exec p_Mypage @tableName= 'Subject',@Fields = '*',108         @orderFields = 'SubjectNo',@where=null,109         @pageSize = 5,@pageIndex = 2,@RecordCount = @a output,110         @PageCount = @b output111     print @a112     print @b113 114 115     --游标116     117 118 119 120  declare sur_stu cursor scroll for121  select StudentName from Student for read only122  open sur_stu123 124  declare @name varchar(20)125  declare @i int126  set @i = 1;127  fetch next from cur_stu into @name128  while(@@FETCH_STATUS<> -1)129  begin130     if(@i = 3)131     begin132         print @name133         set @i=0134         end135     fetch next from cur_stu into @name136     set @i=@i  +1137 138 end139 140 close sur_stu141 deallocate sur_stu142 143 144 --使用游标和查存储过程创建分页  性能最差145 create procedure sqlPage146 @sql nvarchar (4000),147 @pageIndex int,148 @pageSize int,149 @recordCount int output,150 @pageCount int output151 as 152     declare @p1 int 153     exec sp_cursoropen @p1 output,@sql,@scrollopt= 1, @ccopt = 1,@rowcount= @recordCount output154     set @pageCount = CEILING((@recordCount+0.0)/@pageSize)155     exec sp_cursorfetch @p1,16,@pageIndex,@pageSize156     exec sp_cursorclose @p1157 158 declare @a int ,@b int159 exec sqlPage 'select * from student',1,5,@a output,@b output160 print @a 161 print @b