分页存储过程

USE [DGJDB]
GO
/****** 对象:  StoredProcedure [dbo].[DB_UserGetList]    脚本日期: 09/10/2009 20:51:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  ***
-- Create date: 09-04-23
-- Description: 获取用户集
-- =============================================
CREATE PROCEDURE [dbo].[DB_UserGetList]
@recordCount INT OUTPUT,
@strwhere VARCHAR(4000),
@startRecord INT = 0,
@records INT = 10
AS
DECLARE @SQL NVARCHAR(4000),@SQLRECORD NVARCHAR(4000)
SET @SQL = ' '
IF @strwhere <> ''
BEGIN
  SET @SQL = @SQL + @strwhere
END

BEGIN
 
SET @SQLRECORD = ' SELECT @recordCount=COUNT(*) FROM dbo.[User]
       WHERE 1=1 and User_States=0' +@SQL + ''
       EXEC sp_executesql @SQLRECORD,N'@recordCount INT OUTPUT',@recordCount OUTPUT       

IF @records=0
 BEGIN
   SET @SQL = ' SELECT * FROM dbo.[User]
      WHERE 1 = 1 and User_States=0'+@SQL+'
        ORDER BY User_ID ASC '
 END
ELSE
 BEGIN
      SET @SQL = ' SELECT *
  FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY User_ID ASC) AS ROW_NUMBER
    FROM dbo.[User]
       WHERE 1 = 1 and User_States=0'+@SQL+')A
  WHERE ROW_NUMBER > '+LTRIM(STR(@startRecord))+' AND ROW_NUMBER <= '+LTRIM(STR(@records+@startRecord))+''
 END
 EXEC(@SQL)
END