sql server万能分页存储过程

小渣渣 · 发表于 2016-7-8 12:01:22

USE [xxx]
GO
/****** Object: StoredProcedure [dbo].[p_Page] scrip{过滤}t Date: 08/07/2016 11:56:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ITSVSE
-- Create date: 2015/11/04
-- Descrip{过滤}tion: 分页
-- =============================================
ALTER PROCEDURE [dbo].[p_Page]
@PageIndex INT=1,
@PageSize INT=10,
@TbName VARCHAR(50),
@WhereStr NVARCHAR(1000)=NULL,
@OrderBy VARCHAR(50)=' ORDER BY ID',
@Total BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @Total=0
DECLARE @strSQL nvarchar(4000) -- 主Sql
IF @WhereStr='' OR @WhereStr IS NULL
BEGIN
SET @strSQL='SELECT @Total=COUNT(0) FROM ['+@TbName +'] WHERE 1=1 '
EXEC sp_executesql @strSQL,N'@Total BIGINT OUTPUT',@Total OUTPUT;
SET @strSQL='SELECT * FROM ['+@TbName+'] WHERE ID IN (SELECT ID FROM (SELECT ID,ROW_NUMBER() OVER ('+@OrderBy+') AS num FROM ['+@TbName+']) AS settable WHERE num BETWEEN '+STR(((@PageIndex-1)*@PageSize)+1)+' AND '+STR(@PageIndex*@PageSize) +')'
END
ELSE
BEGIN
PRINT 'NOT NULL'
SET @strSQL='SELECT @Total=COUNT(0) FROM ['+@TbName +'] WHERE 1=1 '+@WhereStr
EXEC sp_executesql @strSQL,N'@Total BIGINT OUTPUT',@Total OUTPUT;
SET @strSQL='SELECT * FROM ['+@TbName+'] WHERE ID IN (SELECT ID FROM (SELECT ID,ROW_NUMBER() OVER ('+@OrderBy+') AS num FROM ['+@TbName+'] WHERE 1=1 '+@WhereStr+ ') AS settable WHERE num BETWEEN '+STR((@PageIndex-1)*@PageSize+1)+' AND '+STR(@PageIndex*@PageSize) +')'
END
EXEC(@strSQL)
END

复制代码

sql万能分页存储过程

顾言 · 发表于 2016-7-8 14:29:15

万能的？

		自动登录	找回密码
密码			注册[Register]

[资料] sql server万能分页存储过程