SQLServer通用分页存储过程，字符分隔函数

分页存储过程

sp_QueryPaging

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: -- Create date: -- Description: 分页查询 -- 返回的数据中总是在每条记录前加上行号RowNumber列。 -- 如果未指定@StrSelect参数，相当于设置它为'*' -- 如果未指定@StrFrom参数，不做查询，返回-1 -- 如果未指定@StrWhere参数，则忽略Where条件 -- 如果未指定@StrOrder参数，试着自动获得标识列作为@StrOrder，失败则不做查询，返回-1 -- 如果未指定@PageSize参数，或者它<=0，则返回全部记录 -- 如果未指定@PageIndex参数，会设置它为1，显示第一页数据 -- 参考：shiwenbin http://www.cnblogs.com/virusswb/archive/2009/10/20/1587179.html -- ============================================= ALTER PROCEDURE [dbo].[sp_QueryPaging] @StrSelect NVARCHAR(4000) = '*' , --欲显示的列（多列用逗号分开），例如：id,name @StrFrom NVARCHAR(4000) , --表名称，或者是表连接字符串，多表连接例如:student as s inner join dwinfo as dw on s.dwbh=dw.bh @StrWhere NVARCHAR(4000) = '' , --查询条件,''代表没有条件，单条件或者多条件，多条件例如：name='啊' and id=10 @StrOrder NVARCHAR(4000) , --排序列(多个排序列用逗号分开），例如:id desc,name as @PageSize INT = 0 , --每页显示条数 @PageIndex INT = 1 , --当前页 @RecordCount BIGINT = -1 OUTPUT --返回当前页记录数 AS BEGIN SET NOCOUNT ON ; IF @StrFrom IS NULL BEGIN RETURN -1 END --若没有指定@StrOrder，试着获取标识列 IF @StrOrder IS NULL BEGIN DECLARE @identitycol NVARCHAR(50) SET @identitycol = ( SELECT TOP ( 1 ) COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = @StrFrom AND COLUMNPROPERTY(OBJECT_ID(@StrFrom), COLUMN_NAME, 'IsIdentity') = 1 ) IF @identitycol IS NULL --没有Order By和标识列 BEGIN RETURN -1 END SET @StrOrder = @identitycol END DECLARE @SqlQuery NVARCHAR(4000) IF ( @PageSize <= 0 ) BEGIN SET @SqlQuery=N'SELECT ROW_NUMBER() OVER(ORDER BY ' + @StrOrder + ' ) AS RowNumber,' + @StrSelect + ' FROM ' + @StrFrom ; GOTO Label_exec END IF ( @PageIndex = 1 ) BEGIN IF ( @StrWhere = '' ) BEGIN SET @SqlQuery = N'SELECT TOP ' + CONVERT(NVARCHAR, @PageSize) + ' ROW_NUMBER() OVER(ORDER BY ' + @StrOrder + ' ) AS RowNumber,' + @StrSelect + ' FROM ' + @StrFrom ; END ELSE BEGIN SET @SqlQuery = N'SELECT TOP ' + CONVERT(NVARCHAR, @PageSize) + ' ROW_NUMBER() OVER(ORDER BY ' + @StrOrder + ' ) AS RowNumber,' + @StrSelect + ' FROM ' + @StrFrom + ' WHERE ' + @StrWhere ; END END ELSE BEGIN IF ( @StrWhere = '' ) BEGIN SET @SqlQuery = N'WITH CTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @StrOrder + ' ) AS RowNumber,' + @StrSelect + ' FROM ' + @StrFrom + ' ) SELECT * FROM CTE WHERE ROWNUMBER BETWEEN ' + CONVERT(NVARCHAR, ( ( @PageIndex - 1 ) * @PageSize ) + 1) + ' AND ' + CONVERT(NVARCHAR, @PageIndex * @PageSize) END ELSE BEGIN SET @SqlQuery = N'WITH CTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @StrOrder + ' ) AS RowNumber,' + @StrSelect + ' FROM ' + @StrFrom + ' WHERE ' + @StrWhere + ' ) SELECT * FROM CTE WHERE ROWNUMBER BETWEEN ' + CONVERT(NVARCHAR, ( ( @PageIndex - 1 ) * @PageSize ) + 1) + ' AND ' + CONVERT(NVARCHAR, @PageIndex * @PageSize) END END --set @SqlQuery=@SqlQuery+';select @ItemCount =count(*) from '+@TableName --set @PageCount=@ItemCount/@PageSize --print '共'+@PageConut+'页'+@ItemCount+'条' --print @ItemCount Label_exec: -- PRINT @SqlQuery EXEC (@SqlQuery) SET @RecordCount = @@ROWCOUNT END

字符分隔函数

f_split

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[f_split] ( @String NVARCHAR(4000) , @Delimiter NCHAR(1) ) RETURNS @temptable TABLE ( items NVARCHAR(4000) ) AS BEGIN DECLARE @idx INT DECLARE @slice NVARCHAR(4000) SELECT @idx = 1 IF LEN(@String) < 1 OR @String IS NULL RETURN WHILE @idx != 0 BEGIN SET @idx = CHARINDEX(@Delimiter, @String) IF @idx != 0 SET @slice = LEFT(@String, @idx - 1) ELSE SET @slice = @String IF ( LEN(@slice) > 0 ) INSERT INTO @temptable ( Items ) VALUES ( @slice ) SET @String = RIGHT(@String, LEN(@String) - @idx) IF LEN(@String) = 0 BREAK END RETURN END