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 a
100 order by a.'+@orderFields+' desc) as b
101 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 output
111 print @a
112 print @b
113
114
115 --游标
116
117
118
119
120 declare sur_stu cursor scroll for
121 select StudentName from Student for read only
122 open sur_stu
123
124 declare @name varchar(20)
125 declare @i int
126 set @i = 1;
127 fetch next from cur_stu into @name
128 while(@@FETCH_STATUS<> -1)
129 begin
130 if(@i = 3)
131 begin
132 print @name
133 set @i=0
134 end
135 fetch next from cur_stu into @name
136 set @i=@i +1
137
138 end
139
140 close sur_stu
141 deallocate sur_stu
142
143
144 --使用游标和查存储过程创建分页 性能最差
145 create procedure sqlPage
146 @sql nvarchar (4000),
147 @pageIndex int,
148 @pageSize int,
149 @recordCount int output,
150 @pageCount int output
151 as
152 declare @p1 int
153 exec sp_cursoropen @p1 output,@sql,@scrollopt= 1, @ccopt = 1,@rowcount= @recordCount output
154 set @pageCount = CEILING((@recordCount+0.0)/@pageSize)
155 exec sp_cursorfetch @p1,16,@pageIndex,@pageSize
156 exec sp_cursorclose @p1
157
158 declare @a int ,@b int
159 exec sqlPage 'select * from student',1,5,@a output,@b output
160 print @a
161 print @b
