分页存储过程

ALTER PROCEDURE [dbo].[proc_Hotel_Pagedata] 
    @pageSize int, 
    @pageCurrent int,
    @province varchar(20),
    @city varchar(20),
    @brandId varchar(20)
AS
	declare @sql nvarchar(4000)
BEGIN
	select * from 
	(select ROW_NUMBER() over(ORDER BY hotelId) RowNum, * from nbapisdk_Hotel where 
	province = (CASE WHEN (@province IS NULL) THEN province ELSE @province END) 
	and city = (CASE WHEN (@city IS NULL) THEN city ELSE @city END) 
	and brandId = (CASE WHEN (@brandId IS NULL) THEN brandId ELSE @brandId END)
	)OrderData 
	where RowNum between (@pageCurrent - 1)*@pageSize + 1 and @pageCurrent * @pageSize order by hotelId
	execute(@sql)
END

 

上面是正常的SQL语句，下面是拼接的分页存储过程

 

ALTER PROCEDURE [dbo].[proc_tourol_B2COrder_Pagedata]
    @pageSize int, 
    @pageIndex int,
    @count int out,
    @sqlwhere nvarchar(200)
    
AS
declare @sql nvarchar(4000)
declare @sql2 nvarchar(4000)
BEGIN
	set @sql=
	'select * from 
	(select ROW_NUMBER() over(ORDER BY Orderid) RowNum, * from tourol_B2COrder '+@sqlwhere+'
	)OrderData 
	where RowNum between '+CONVERT(nvarchar(100),@pageIndex)+'*'+CONVERT(nvarchar(100),@pageSize)
	+' + 1 and ('+CONVERT(nvarchar(100),@pageIndex)+'+1) * '+CONVERT(nvarchar(100),@pageSize)
	+' order by Orderid'
	print @sql
	exec (@sql)
	
	set @sql2=
	'select @count=count(*) from 
	(select ROW_NUMBER() over(ORDER BY Orderid) RowNum, * from tourol_B2COrder '+@sqlwhere+'
	)OrderData'
	print @sql2
	exec sp_executesql @sql2,N'@count INT OUT',@count=@count OUT
	print @count
END

 

这里需要注意的是传出参数的写法