1.存储过程中的 输出参数
create proc sp_pager @totalcount int output,@totalpagecount int output
2.对变量的赋值（set\select）
select适合对多变量赋值，而set适合给单变量赋值
select可以将多个值赋给自己，取最后一个，而set只能给单个值赋值
select对赋值为空的值保持原来的值，而set对赋值为空的值结果为NULL
set\select赋值后原来的查询执行不返回只将结果给赋值的变量。
(1).select @name=name,@age=age from t_person
(2).select name from (select @name=name from t_person)
3.控制语句
(1).if语句
if 条件 or 条件
begin
sql语句
end
else
begin
sql语句
end
if条件可以嵌套
if @primarykey is null or @primarykey=''
begin
select @primarykey=c.name from sys.all_columns c join sys.all._objects o on c.object_id=o.object_id
where o.name=@tablename and c.column_id=1
end
(2).while语句
while 条件 or 条件
begin
sql语句
end
while charindex(',',@order)>0 or charindex(',',@order)>0
begin
set @order=replace(@order,',' ,',')
end
4.charindex函数:查找字符
charindex(@primarykey,@order) => @order中是否存在@primarykey,返回为int
5.set nocount on:不返回影响的行 set oncount off:返回影响的行
6.isnull函数:空值赋值
isnull(@totalcount,'')：如果@totalcount不为NULL则返回@totalcount否则返回''
7.rtrim\ltrim函数:(右边，左边)去空格
8.replace函数:替换
replace(rtrim(ltrim(@fieldlist)),' ','')：将@fieldlist中前后中的空格都去掉
9.round()\floor()\ceiling()函数
round() 遵循四舍五入把原值转化为指定小数位数，如：round(1.45,0) = 1;round(1.55,0)=2
floor()向下舍入为指定小数位数 如：floor(1.45,0)= 1;floor(1.55,0) = 1
ceiling()向上舍入为指定小数位数 如：ceiling(1.45,0) = 2;ceiling(1.55,0)=210.
10.substring函数:获取子字符串
substring(@new_order2,1,len(@new_order2)-1):获取@new_order2中从1到最后位置的字符串
11.'<>'/'!=':不等于
12.str函数:返回一个数字的指定长度的字符串
str(@number,3,0):返回@number中整数部分为3，小数部分为0的字符串
也可使用str(@number)则将数字转换为字符串
1 USE [YLBX]
2 GO
3
4 /****** Object: StoredProcedure [dbo].[Sp_Pager] Script Date: 03/24/2013 23:10:40 ******/
5 SET ANSI_NULLS ON
6 GO
7
8 SET QUOTED_IDENTIFIER ON
9 GO
10
11
12 -- =============================================
13 -- Author: <Author,,Name>
14 -- Create date: <Create Date,,>
15 -- Description: <Description,,>
16 -- =============================================
17 CREATE PROCEDURE [dbo].[Sp_Pager]
18 -- Add the parameters for the stored procedure here
19 @TableName VARCHAR(200), --表名
20 @FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*
21 --@PrimaryKey VARCHAR(100), --单一主键或唯一值键
22 @Where VARCHAR(2000)='', --查询条件 不含'where'字符，如id>10 and len(userid)>9
23 @Order VARCHAR(1000)='', --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
24 --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
25 --@SortType INT=1, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
26 --@RecorderCount INT=0, --记录总数 0:会返回总记录
27 @PageSize INT=20, --每页输出的记录数
28 @PageIndex INT=1, --当前页数
29 @TotalCount INT OUTPUT , --记返回总记录
30 @TotalPageCount INT OUTPUT --返回总页数
31 AS
32 BEGIN
33 -- SET NOCOUNT ON added to prevent extra result sets from
34 -- interfering with SELECT statements.
35 --得到主键
36 declare @PrimaryKey varchar(2000) --单一主键或唯一值键
37 declare @SortType int
38 declare @RecorderCount int
39 Select @SortType=1
40 select @RecorderCount=0
41 select @PrimaryKey=COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@TableName
42 if @PrimaryKey is null or @PrimaryKey=''
43 begin
44 select @PrimaryKey=c.name from sys.all_columns c join sys.all_objects o on c.object_id=o.object_id
45 where o.name=@TableName and c.column_id=1
46 end
47 --
48 if @Order<>''
49 begin
50 if CHARINDEX(@PrimaryKey,@Order)=0
51 begin
52 if CHARINDEX('asc',@Order)=0 and charindex('desc',@Order)=0
53 select @order=@order+' asc'
54 select @Order=@Order+','+@PrimaryKey+' asc'
55 select @sorttype=3
56 end
57 else
58 begin
59 if CharIndex(',',@Order)=0
60 begin
61 if CHARINDEX('desc',@order)>0
62 begin
63 select @sorttype=2
64 end
65 end
66 end
67 end
68 --
69 SET NOCOUNT ON;
70
71 -- Insert statements for procedure here
72 IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
73 SET @Order = RTRIM(LTRIM(@Order))
74 SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
75 SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
76 WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0
77 BEGIN
78 SET @Order = REPLACE(@Order,', ',',')
79 SET @Order = REPLACE(@Order,' ,',',')
80 END
81 IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = ''
82 or ISNULL(@PrimaryKey,'') = ''
83 or @SortType < 1 or @SortType >3
84 or @RecorderCount < 0 or @PageSize < 0 or @PageIndex < 0
85 BEGIN
86 PRINT('ERR_00')
87 RETURN
88 END
89 IF @SortType = 3
90 BEGIN
91 IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
92 BEGIN PRINT('ERR_02') RETURN END
93 END
94 DECLARE @new_where1 VARCHAR(1000)
95 DECLARE @new_where2 VARCHAR(1000)
96 DECLARE @new_order1 VARCHAR(1000)
97 DECLARE @new_order2 VARCHAR(1000)
98 DECLARE @new_order3 VARCHAR(1000)
99 DECLARE @Sql VARCHAR(8000)
100 DECLARE @SqlCount NVARCHAR(4000)
101 IF ISNULL(@where,'') = ''
102 BEGIN
103 SET @new_where1 = ' '
104 SET @new_where2 = ' Where '
105 END
106 ELSE
107 BEGIN
108 SET @new_where1 = ' Where ' + @where
109 SET @new_where2 = ' Where ' + @where + ' AND '
110 END
111 IF ISNULL(@order,'') = '' or @SortType = 1 or @SortType = 2
112 BEGIN
113 IF @SortType = 1
114 BEGIN
115 SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC'
116 SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC'
117 END
118 IF @SortType = 2
119 BEGIN
120 SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC'
121 SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC'
122 END
123 END
124 ELSE
125 BEGIN
126 SET @new_order1 = ' orDER BY ' + @Order
127 END
128
129 IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
130 BEGIN
131 SET @new_order1 = ' orDER BY ' + @Order
132 SET @new_order2 = @Order + ','
133 SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
134 SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
135 SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
136 IF @FieldList <> '*'
137 BEGIN
138 SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
139 SET @FieldList = ',' + @FieldList
140 WHILE CHARINDEX(',',@new_order3)>0
141 BEGIN
142 IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
143 BEGIN
144 SET @FieldList =
145 @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
146 END
147 SET @new_order3 =
148 SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
149 END
150 SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
151 END
152 END
153
154 SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
155 + CAST(@PageSize AS VARCHAR)+') FROM (Select * FROM ' + @TableName + @new_where1+') AS T'
156 IF @RecorderCount = 0
157 BEGIN
158 EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
159 @TotalCount OUTPUT,@TotalPageCount OUTPUT
160 END
161 ELSE
162 BEGIN
163 Select @TotalCount = @RecorderCount
164 END
165 IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
166 BEGIN
167 SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
168 END
169 IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
170 BEGIN
171 IF @PageIndex = 1 --返回第一页数据
172 BEGIN
173 SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
174 + @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1
175 END
176 IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
177 BEGIN
178 SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
179 + 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
180 + ' ' + @FieldList + ' FROM '
181 + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
182 + @new_order1
183 END
184 END
185 ELSE
186
187 BEGIN
188 IF @SortType = 1 --仅主键正序排序
189 BEGIN
190 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
191 BEGIN
192 SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
193 + @TableName + @new_where2 + @PrimaryKey + ' > '
194 + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
195 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
196 + ' FROM ' + @TableName
197 + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
198 END
199 ELSE --反向检索
200 BEGIN
201 SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
202 + 'Select TOP ' + STR(@PageSize) + ' '
203 + @FieldList + ' FROM '
204 + @TableName + @new_where2 + @PrimaryKey + ' < '
205 + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
206 + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
207 + ' FROM ' + @TableName
208 + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
209 + ' ) AS TMP ' + @new_order1
210 END
211 END
212 IF @SortType = 2 --仅主键反序排序
213 BEGIN
214 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
215 BEGIN
216 SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
217 + @TableName + @new_where2 + @PrimaryKey + ' < '
218 + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
219 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
220 +' FROM '+ @TableName
221 + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
222 END
223 ELSE --反向检索
224 BEGIN
225 SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
226 + 'Select TOP ' + STR(@PageSize) + ' '
227 + @FieldList + ' FROM '
228 + @TableName + @new_where2 + @PrimaryKey + ' > '
229 + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
230 + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
231 + ' FROM ' + @TableName
232 + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
233 + ' ) AS TMP ' + @new_order1
234 END
235 END
236 IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理
237 BEGIN
238 IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
239 BEGIN PRINT('ERR_02') RETURN END
240 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
241 BEGIN
242 SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
243 + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
244 + ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
245 + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
246 + @new_order2 + ' ) AS TMP ' + @new_order1
247 END
248 ELSE --反向检索
249 BEGIN
250 SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
251 + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
252 + ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
253 + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
254 + @new_order1 + ' ) AS TMP ' + @new_order1
255 END
256 END
257 END
258 PRINT(@SQL)
259 EXEC(@Sql)
260 END
261
262
263 GO
浙公网安备 33010602011771号
