SQL codeUSE [LHZWeb_WWW]GO/****** Object: StoredProcedure [dbo].[SelectSaleOrderByLike] Script Date: 05/29/2012 09:52:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-------------------------------------------------- Generated By: dell using CodeSmith 5.0.0.0-- Template: StoredProcedures.cst-- Procedure Name: [dbo].[SelectSaleOrderByLike]-- Date Generated: 2011年8月19日-- Alter By: Li.Lei Alter Date: 12/19/2011------------------------------------------------ALTER PROCEDURE [dbo].[SelectSaleOrderByLike]( @OrderNo varchar(20), @Email varchar(50), @SKU varchar(20), @GoodsName nvarchar(200), @OrderDateTo datetime, @OrderDateFrom datetime, @Consignee nvarchar(20), @paystatus varchar(5), @shipment nvarchar(5), @IsGiftCard char(1), @NotRmk varchar(20), @PageSize int, @Page int, @TotalNum int out )asdeclare @sql nvarchar(2500),@r_sql nvarchar(1000) = '';----------- 公用查询条件开始 -----------------------if(ISNULL(@OrderNo,'') != '') begin set @r_sql += ' and OrderNo like '''+'%' + @OrderNo +'%'+'''';endif(DATEDIFF(DAY,ISNULL(@OrderDateFrom,0),'1900-01-1 0:00:00') != 0) begin set @r_sql += ' and @OrderDateFrom <= OrderDate ';endif(DATEDIFF(DAY,ISNULL(@OrderDateTo,0),'1900-01-1 0:00:00') != 0) begin set @r_sql += ' and @OrderDateTo >= OrderDate';endif(ISNULL(@Consignee,'') != '') begin set @r_sql += ' and Consignee like '''+'%' + @Consignee +'%'+''''endif(ISNULL(@Email,'') != '') begin set @r_sql += ' and Email like [email protected]+'%'+'''';endif(ISNULL(@paystatus,'-1') != '-1') begin set @r_sql += ' and payStatus = ' + str(@paystatus);endif(ISNULL(@shipment,'-1') != '-1') begin set @r_sql += ' and shipment = ' + @shipment;endif(ISNULL(@IsGiftCard,'') != '') begin set @r_sql += ' and IsGiftCard = ''' + @IsGiftCard+'''';end--未备注过滤条件 add by lilei on2011-10-19if (@NotRmk!='') begin if (@NotRmk='STO') begin set @r_sql += ' and Shipment=1 and ISNULL(SalesOrder.Remark,'''')='''''; end if (@NotRmk='COE') begin set @r_sql += ' and Shipment=3 and ISNULL(SalesOrder.Remark,'''')='''''; end if (@NotRmk='CODZJS') begin set @r_sql += ' and Shipment=4 and ISNULL(SalesOrder.Remark,'''')='''''; endend------------ end ------------------------------------ if (ISNULL(@SKU,'')='' and ISNULL(@GoodsName,'')='') beginset @sql = ' Select @TotalNum = count(*) from SalesOrder Where Valid = 1';set @sql += @r_sql;exec sp_executesql @sql,[email protected] int output,@OrderDateFrom datetime,@OrderDateTo datetime', @TotalNum output,@OrderDateFrom,@OrderDateTo; set @sql = 'Select T2.* , (Select Name from Area Where Area.[ID]=T2.Province) ProvinceName, (Select Name FROM Area Where Area.[ID]=T2.City) CityName, (Select Name FROM Area Where Area.[ID]=T2.District) DistrictName from (Select SalesOrder.*,c.Amount CouponAmount,(ROW_NUMBER() over(Order by SalesOrder.[ID] desc)) as rn from SalesOrderleft join Coupon c on c.CouponNo=SalesOrder.CouponNo Where SalesOrder.Valid=1'set @sql += @r_sql;set @sql += ')T2'; -- Where rn > (@Page-1)[email protected] and rn<[email protected][email protected][email protected]if (@PageSize!=-1)beginset @sql += ' Where rn > (@Page-1)[email protected] and rn<[email protected][email protected]';endexec sp_executesql @sql,[email protected] int,@PageSize int,@OrderDateFrom datetime,@OrderDateTo datetime', @Page,@PageSize,@OrderDateFrom,@OrderDateTo; end else begin if(ISNULL(@GoodsName,'') != '') begin set @r_sql += ' and OrderItem.GoodsName like [email protected]+'%'+'''';endif(ISNULL(@SKU,'') != '')beginset @r_sql += ' and OrderItem.SKU like '''+'%' + @SKU + '%'+'''';endset @r_sql += N' and OrderItem.Valid = 1 and SalesOrder.Valid = 1';set @sql = ' Select @TotalNum = count(*) from SalesOrder,OrderItem Where OrderItem.SalesOrderID=SalesOrder.ID';set @sql += @r_sql;exec sp_executesql @sql,[email protected] int output,@OrderDateFrom datetime,@OrderDateTo datetime', @TotalNum output,@OrderDateFrom,@OrderDateTo; set @sql = ' Select T2.* , (Select Name FROM Area WHERE ID=T2.Province) ProvinceName, (Select Name FROM Area WHERE ID=T2.City) CityName, (Select Name FROM Area WHERE ID=T2.District) DistrictNamefrom ( Select T1.*,(ROW_NUMBER() over(Order by (select ID from T1) desc)) as rn from ( Select s.[ID],s.OrderNo,s.OrderDate,s.Total,s.Consignee,s.MemberName,s.Province,s.City,s.SendTime,s.MemberNote,s.Remark,s.CouponNo,c.Amount,s.District CouponAmount from SalesOrder s,OrderItem o left join Coupon c on c.CouponNo=(select CouponNo from SalesOrder) Where o.SalesOrderID=s.ID'set @sql += @r_sql;set @sql += ' )T1 )T2' -- Where rn > (@Page-1)[email protected] and rn<[email protected][email protected][email protected]if (@PageSize!=-1)beginset @sql += ' Where rn > (@Page-1)[email protected] and rn<[email protected][email protected]';endexec sp_executesql @sql,[email protected] int,@PageSize int,@OrderDateFrom datetime,@OrderDateTo datetime', @Page,@PageSize,@OrderDateFrom,@OrderDateTo; end--end