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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
