俺写的 万能存储过程解决方案
俺写的 万能存储过程
CREATE PROCEDURE upPaging
@PageIndex INT,
@PageSize INT,
@tableName varchar(30), ---表名称
@colNames varchar(50), --查询表的列
@orderby varchar(50), --排序规则
@oredrType varchar(10) = 'ASC' --排序是降序,还是升序
AS
DECLARE @StartData INT
DECLARE @EndData INT
SET @StartData = (@PageIndex-1)*@PageSize+1
SET @EndData=@PageSize*@PageIndex
DECLARE @strSQL VARCHAR(1000)
SET @strSQL='SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY '+@orderby+' '+@oredrType+') AS A,'+@colNames+' FROM '+@tableName+') AS P WHERE A
BETWEEN '+STR(@StartData)+' AND '+STR(@EndData)+''
PRINT (@strSQL)
EXEC (@strSQL)
EXEC upPaging 1,5,Student,[*],id,[DESC]
------解决方案--------------------
CREATE PROCEDURE upPaging
@PageIndex INT,
@PageSize INT,
@tableName varchar(30), ---表名称
@colNames varchar(50), --查询表的列
@orderby varchar(50), --排序规则
@oredrType varchar(10) = 'ASC' --排序是降序,还是升序
AS
DECLARE @StartData INT
DECLARE @EndData INT
SET @StartData = (@PageIndex-1)*@PageSize+1
SET @EndData=@PageSize*@PageIndex
DECLARE @strSQL VARCHAR(1000)
SET @strSQL='SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY '+@orderby+' '+@oredrType+') AS A,'+@colNames+' FROM '+@tableName+') AS P WHERE A
BETWEEN '+STR(@StartData)+' AND '+STR(@EndData)+''
PRINT (@strSQL)
EXEC (@strSQL)
EXEC upPaging 1,5,Student,[*],id,[DESC]
------解决方案--------------------
- SQL code
CREATE PROCEDURE [dbo].[sp_common_paging] @pageIndex INT = 1, @pageSize INT = 10, @tableName VARCHAR(256) = '', @orderby VARCHAR(256) = '', @fields VARCHAR(256) = '', @condition NVARCHAR(MAX) = '', @recordCount INT OUTPUT AS BEGIN IF @tableName IS NULL OR @tableName = '' BEGIN RAISERROR('查询的数据表不为能空!', 16, 1) RETURN END IF NOT EXISTS( SELECT * FROM sys.tables t WHERE t.[name] = @tableName ) BEGIN RAISERROR('数据表不存在!', 16, 1) RETURN END SELECT @tableName = QUOTENAME(@tableName) IF @pageIndex IS NULL OR @pageIndex = '' SELECT @pageIndex = 1 IF @pageSize IS NULL OR @pageSize = '' SET @pageSize = 10 IF @fields = '' OR @fields IS NULL SET @fields = '*' IF @orderby IS NULL OR @orderby = '' BEGIN DECLARE @count SMALLINT SELECT @count = COUNT(1) FROM sys.COLUMNS c INNER JOIN sys.index_columns ic ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id INNER JOIN sys.indexes i ON c.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND i.is_primary_key = '1' AND i.[object_id] = OBJECT_ID(@tableName) IF @count > 0 BEGIN DECLARE @fieldName VARCHAR(256) DECLARE #pkCursor CURSOR STATIC FOR SELECT c.NAME FROM sys.COLUMNS c INNER JOIN sys.index_columns ic ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id INNER JOIN sys.indexes i ON c.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND i.is_primary_key = '1' AND i.[object_id] = OBJECT_ID(@tableName) OPEN #pkCursor FETCH NEXT FROM #pkCursor INTO @fieldName WHILE @@FETCH_STATUS = 0 BEGIN IF (@orderby IS NULL OR @orderby = '') SET @orderby = '' + @fieldName ELSE SET @orderby = @orderby + ',' + @fieldName FETCH NEXT FROM #pkCursor INTO @fieldName END CLOSE #pkCursor DEALLOCATE #pkCursor END ELSE BEGIN SELECT @orderby = [NAME] FROM sys.[columns] c WHERE c.[object_id] = OBJECT_ID(@tableName) AND c.column_id = 1 END END PRINT(@orderby) DECLARE @sql NVARCHAR(MAX) DECLARE @sqlCount NVARCHAR(MAX) IF @pageIndex = 1 BEGIN SELECT @sql = 'select top(@pageSize) ' + @fields + ' from ' + @tableName SET @sqlCount = 'select @recordCount=count(1) from ' + @tableName IF @condition IS NOT NULL AND @condition <> '' BEGIN SET @sql = @sql + ' where ' + @condition SET @sqlCount = @sqlCount + ' where ' + @condition END IF @orderby IS NOT NULL AND @orderby <> '' SET @sql = @sql + ' order by ' + @orderby EXEC sp_executesql @sql, N'@pageSize int,@orderby nvarchar(max)', @pageSize, @orderby EXEC sp_executesql @sqlCount, N'@recordCount int out', @recordCount OUT RETURN END ELSE BEGIN IF @fields IS NULL OR @fields = '' OR @fields = '*' SELECT @fields = dbo.getFields(@tableName) SELECT @sql = 'select ' + @fields + ' from (select row_number() over(order by @orderby) as rowIndex,' + @fields + ' from ' + @tableName SET @sqlCount = 'select @recordCount=count(1) from ' + @tableName IF @condition IS NOT NULL AND @condition <> '' BEGIN SET @sql = @sql + ' where ' + @condition SET @sqlCount = @sqlCount + ' where ' + @condition END SET @sql = @sql + ') t where rowIndex between ((@pageIndex-1)*@pageSize+1) and (@pageIndex*@pageSize)' EXEC sp_executesql @sql, N'@orderby varchar(max),@pageIndex int,@pageSize int', @orderby, @pageIndex, @pageSize EXEC sp_executesql @sqlCount, N'@recordCount int out', @recordCount OUT END END