分页存储过程

 /*
功能：
名称：[dbo].[abc]
*/

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'abc' AND user_name(uid) = 'dbo')
 DROP PROCEDURE [dbo].[szum2007_fetchget_ty_gps_car_message]
GO

CREATE PROCEDURE [dbo].[abc]
(   @pagesize int,
 @pageindex int,
 @message_status int,
 @message_direction int,
    @db_created_date varchar(50)
  
)
--WITH ENCRYPTION
AS

DECLARE @where   nvarchar(max)
Declare @sqlstr  nvarchar(max)
DECLARE @start int ,@end int
set @where=N' where convert(varchar(50),db_created_date,23)= ''' +CAST(@db_created_date AS nvarchar(36))+ ''''
if @message_status!<0
begin
SET @where = @where + N' AND message_status = ''' +CAST(@message_status AS nvarchar(36))+ ''''
end
if @message_direction>0
begin
SET @where = @where + N' AND message_direction = ''' +CAST(@message_direction AS nvarchar(36))+ ''''
end

SET  @start =(@pageindex-1)*@pagesize+1
SET  @end   =(@pageindex-1)*@pagesize+@pagesize
set @sqlstr=N'SELECT ROW_NUMBER() OVER (ORDER BY fid) AS Row,name,sage_stus,age_direc,sage_content,db_created_date,fid from dbo.message
 INNER JOIN 
 Car ON ty_gps_car_message.car_id=CG_Car.C_carid '+@where
set @sqlstr=N' SELECT *,(select count(*) from ty_gps_car_message INNER JOIN 
 Car ON message.car_id=CG_Car.C_carid '+@where+N') as abcrow FROM('+@sqlstr+ N')as temp where temp.Row between  ''' +CAST(@start AS nvarchar(36))+ ''''+N'and ''' +CAST(@end AS nvarchar(36))+ ''''
EXEC SP_EXECUTESQL @sqlstr