sql 分页

最新推荐文章于 2023-11-10 08:22:10 发布

这个是谁呀

最新推荐文章于 2023-11-10 08:22:10 发布

阅读量142

点赞数

分类专栏： SQL Server

本文链接：https://blog.csdn.net/qq_36611278/article/details/88863512

版权

SQL Server 专栏收录该内容

23 篇文章 1 订阅

订阅专栏

分页

ALTER procedure [dbo].[procMaterialOrderItems_log_AllList]
    @session varchar(30) ,
    @ip varchar(30) ,
	@order_no varchar(225) ,--生产任务单编号
	@material_receipt_no varchar(225),--收料通知请检单
	@product_no varchar(225) ,--零部件编号
	@operator varchar(200),--操作人
	@begin_date varchar(50),
	@end_date varchar(50),
    @page_size int ,
    @page_index int ,
	@total int output,
    @rescode int = -1 output ,
    @resdesc varchar(200) = '' output
as
begin try
	exec __Check @session,null

	if @begin_date='' set @begin_date=null
	if @end_date='' set @end_date=null

	declare @SkipSize int
	set @SkipSize = @page_size * ( @page_index - 1 );

	declare @sql nvarchar(max),@sqlwhere nvarchar(max),@fields nvarchar(max),@strwhere varchar(max)

	set @sqlwhere=dbo.__SQL_CONDITION(@sqlwhere,'orderNo',quotename('%'+@order_no+'%',''''),'like','and');
	set @sqlwhere=dbo.__SQL_CONDITION(@sqlwhere,'quality_checking_no',quotename('%'+@material_receipt_no+'%',''''),'like','and');
	set @sqlwhere=dbo.__SQL_CONDITION(@sqlwhere,'tFNumberCP',quotename('%'+@product_no+'%',''''),'like','and');
	set @sqlwhere=dbo.__SQL_CONDITION(@sqlwhere,'operatorName',quotename('%'+@operator+'%',''''),'like','and');
	set @sqlwhere=dbo.__SQL_CONDITION(@sqlwhere,'create_date',quotename(''+@begin_date+'',''''),'>=','and');
	set @sqlwhere=dbo.__SQL_CONDITION(@sqlwhere,'create_date',quotename(''+@end_date+'',''''),'<=','and');				
	set @sqlwhere=dbo.__SQL_WHERE(@sqlwhere)
	if @sqlwhere = '' set @strwhere = ' where ' else set @strwhere = @sqlwhere + ' and '
	exec @total=__SQL_GetCount 'v_material_order_items_log',@sqlwhere

	set @fields='top '+ cast(@page_size as varchar) + 'id,orderNo as order_no,tFNumberCP as product_no,tFNumberWL as item_no,spec,quality_checking_no,datet as manufacture_date,batchNo,qty,create_date,repertoryName,positionName,productName,ItemName,operatorName,item_key,order_key'
	set @sql='select distinct '+ @fields + ' from dbo.v_material_order_items_log '+@strwhere+'  id not in (select top '+cast(@SkipSize as varchar)+'id from dbo.v_material_order_items_log  '+@sqlwhere+' order by create_date desc )order by create_date desc '
	exec sp_executesql @sql; 
		
	set @rescode = 0;
    set @resdesc = 'success';
end try	
begin catch
	exec __Exception '获取领料单日志列表失败',@rescode output,@resdesc output
end catch;