SQL分页参数传值 在存储过程中使用 动态SQL实现

 PROCEDURE [dbo].[SP_GetList]
(
   @ParkUserId varchar(100),
   @arrearsStatus int,
   @lastID int=0,  --最小ID
   @pageSize int=10, --每页显示记录数
   @CityCode varchar(50)
   )
AS
    declare  @DICT_Berth  nvarchar(100);
   declare  @strSQL0 nvarchar(max);--定义变量,注意类型
BEGIN
 
 set @DICT_Berth='DICT_Berth_'+@CityCode+'';

   IF(@arrearsStatus IS not NULL AND @arrearsOrderCode IS NULL)
    
     BEGIN
     set   @strSQL0='select top ('+convert(nvarchar(100),@pageSize)+')
     
     CONVERT(varchar(100), TB.StartTime,120) as  StartTime
     FROM TRA_Order AS tao 
     
     where tao.ParkUserId='+@ParkUserId+' and
     tao.AStatus= '+convert(nvarchar(100),@arrearsStatus)+' 
     AND  (( '+convert(nvarchar(100),@lastID)+' > 0 AND tao.OrderId < '+convert(nvarchar(100),@lastID)+') OR '+convert(nvarchar(100),@lastID)+'=0 )
     ORDER BY  tao.OrderId , tao.AddTime DESC
     ';

    END 
    
    

END 
    begin transaction
    begin try
    BEGIN
    

    EXECUTE SP_EXECUTESQL @strSQL0 ; 
    END
   commit transaction
  end try 


begin catch

 print '执行存储异常'

 rollback transaction
  
 end catch

 

 

where (( @lastID > 0 AND Meb_ID < @lastID) OR @lastID=0 )  and
 CONVERT(varchar(100), MB.AddTime, 23)>CONVERT(varchar(100), @StartTime, 23) 
 AND 
 CONVERT(varchar(100), MB.AddTime, 23)<CONVERT(varchar(100), @EndTime, 23)

 

 

(( '+convert(nvarchar(100),@lastID)+' > 0 AND OrderID < '+convert(nvarchar(100),@lastID)+') OR '+convert(nvarchar(100),@lastID)+'=0 )
     ORDER BY OrderID ,AddTime DESC