SQL 游标 contact_cursor 使用示例

declare @cus_no nvarchar(50);
declare @corpaddress1 nvarchar(255);

DECLARE contact_cursor CURSOR FOR
select CUS_NO,ShipAddr from [HMOOSDB].[dbo].[Tbl_newCust] where ShipAddr is not null or ShipAddr !=''
OPEN contact_cursor
    FETCH NEXT FROM contact_cursor INTO @cus_no,@corpaddress1
    WHILE @@FETCH_STATUS = 0
    BEGIN
--        select @corpaddress1=ShipAddr from dbo.Tbl_newCust where CUS_NO = @cus_no
        if @corpaddress1 != '' and @cus_no != ''
        begin
            update [HMOOSDB].[dbo].[Tbl_Customer5] set CorpAddress=@corpaddress1 where ERPNO = @cus_no
        end
--
--        if(@corpname1 is null or @corpname1='') 
--            set @corpname1=@corpname2
--        update Tbl_Customer set [CorpName]=@corpname1 where [ERPNO]=@cus_no
    set @corpaddress1 = ''
    set @cus_no = ''
    FETCH NEXT FROM contact_cursor INTO @cus_no,@corpaddress1
    END
Close contact_cursor
deallocate contact_cursor

ALTER PROCEDURE [dbo].[CustServiceSearchNew] 
    -- Add the parameters for the stored procedure here
    @BrandID int ,
    @PRoleID int ,
    @PCustomerNO nvarchar(50),
    @PERPSO nvarchar(50),
    @PStyleNO nvarchar(50),
    @POrderTimeStart datetime,
    @POrderTimeEnd datetime
AS
BEGIN
    SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Tmp') IS NOT NULL
begin
    --删除临时表#Tmp
    drop table #Tmp
end
--创建临时表#Tmp
create table #Tmp 
(
ID int IDENTITY (1,1) not null,
ERPSONO nvarchar(50),
OrderNO nvarchar(50),
ProduceNO nvarchar(50),
StyleNOs nvarchar(max),
TotalProduceQty int,
OrderTime datetime,
CustomerNO nvarchar(50),
CustomerName nvarchar(50),
OrderStatus nvarchar(50),
ShipToCmpName nvarchar(200),
ShipToAddr nvarchar(255),
ShipToName nvarchar(50),
ShipToTel nvarchar(200),
ShippingNO nvarchar(50),
ShipToType nvarchar(50),
ShipToPayType nvarchar(50),
ShipToExpressName nvarchar(50),
ShipToExpressNO nvarchar(100),
ShipDate datetime
);

declare @ORDERNO nvarchar(50);
declare @CustomerNO nvarchar(50);
declare @CustomerName nvarchar(50);
declare @CustOrderNO nvarchar(50);
declare @ERPSONO nvarchar(50);
declare @ShipCmpNO nvarchar(50);
declare @ShipCmpName nvarchar(50);
declare @ShipAddress nvarchar(200);
declare @ShipContactName nvarchar(50);
declare @ShipContactTel nvarchar(50);
declare @SysStatusID int;
declare @CreateTime datetime;
declare @ProduceNO nvarchar(50);
declare @ReleaseNO int;
declare @DeliveryAddrID int;
declare @CustConfirmDate datetime;

declare @ShippingNO nvarchar(50);
declare @ShipToType nvarchar(50);
declare @ShipToPayType nvarchar(50);
declare @ShipToExpressName nvarchar(50);
declare @ShipToExpressNO nvarchar(100);
declare @ShipDate datetime;

declare @OrderSysStatus nvarchar(50);

declare @inCustomerNO nvarchar(50);
declare @inERPSONO nvarchar(50);
if(@PCustomerNO='')
begin
    set @inCustomerNO='%';
end
else
begin
    set @inCustomerNO=@PCustomerNO;
end
if(@PERPSO='')
begin
    set @inERPSONO='%';
end
else
begin
    set @inERPSONO=@PERPSO;
end

DECLARE contact_cursor CURSOR FOR
--select ORDERNO,CustomerNO,CustomerName,CustOrderNO,ERPSONO,ShipCmpNO,ShipCmpName,ShipAddress,ShipContactName,ShipContactTel,SysStatusID,CreateTime from [Tbl_Order] Where BrandID = @BrandID
SELECT     o.OrderNO, o.CustomerNO, o.CustomerName, o.CustOrderNO, o.ERPSONO, o.ShipCmpNO, o.ShipCmpName, o.ShipAddress, o.ShipContactName, 
                      o.ShipContactTel, pop.SysStatusID, o.CreateTime, pop.ProduceNO, - 1 AS ReleaseNO, - 1 AS DeliveryAddrID, o.CustConfirmDate
FROM         Tbl_Order AS o INNER JOIN
                      Tbl_ProduceOrderProduct AS pop ON o.OrderNO = pop.OrderNO
WHERE     (o.BrandID = 1) AND (pop.ProduceNO NOT IN
                          (SELECT DISTINCT ProduceNO
                            FROM          Tbl_ProduceRelease)) AND (o.ERPSONO LIKE @inERPSONO) AND (o.CustomerNO LIKE @inCustomerNO) AND 
                      (o.CreateTime >= CONVERT(nvarchar(50), @POrderTimeStart, 101)) AND (o.CreateTime < CONVERT(nvarchar(50), @POrderTimeEnd, 
                      101))
UNION
SELECT     pr.OrderNO, o.CustomerNO, o.CustomerName, o.CustOrderNO, pr.ERPSONO, sa.DeliveryCorpName AS ShipCmpNO, 
                      sa.DeliveryCorpName AS ShipCmpName, sa.DeliveryAddr AS ShipAddress, sa.ContactPerson AS ShipContactName, sa.ContactTel AS ShipContactTel, 
                      o.SysStatusID, o.CreateTime, pop.ProduceNO, MIN(pr.ReleaseNO) AS ReleaseNO, MIN(pr.DeliveryAddrID) AS DeliveryAddrID, o.CustConfirmDate
FROM         Tbl_ProduceRelease AS pr INNER JOIN
                      Tbl_ShippingAddr AS sa ON pr.DeliveryAddrID = sa.ID INNER JOIN
                      Tbl_Order AS o ON pr.OrderNO = o.OrderNO inner join
                      dbo.Tbl_ProduceOrderProduct as pop on pr.ProduceNO=pop.ProduceNO
WHERE     (o.BrandID = 1) AND (o.ERPSONO LIKE @inERPSONO) AND (o.CustomerNO LIKE @inCustomerNO) AND (o.CreateTime >= CONVERT(nvarchar(50), 
                      @POrderTimeStart, 101)) AND (o.CreateTime < CONVERT(nvarchar(50), @POrderTimeEnd, 101))
GROUP BY pr.OrderNO, o.CustomerNO, o.CustomerName, o.CustOrderNO, pr.ERPSONO, sa.DeliveryCorpName, sa.DeliveryCorpName, sa.DeliveryAddr, 
                      sa.ContactPerson, sa.ContactTel, o.SysStatusID, o.CreateTime, pr.ProduceNO, pop.ProduceNO, o.CustConfirmDate
ORDER BY o.OrderNO, pop.ProduceNO, ReleaseNO
OPEN contact_cursor
FETCH NEXT FROM contact_cursor INTO @ORDERNO,@CustomerNO,@CustomerName,@CustOrderNO,@ERPSONO,@ShipCmpNO,@ShipCmpName,@ShipAddress,@ShipContactName,@ShipContactTel,@SysStatusID,@CreateTime,@ProduceNO,@ReleaseNO,@DeliveryAddrID,@CustConfirmDate
WHILE @@FETCH_STATUS = 0
BEGIN
    declare @StyleNOSUM nvarchar(max);
    declare @TotalProduceQtySUM int;
    IF OBJECT_ID('tempdb..#Tmp2') IS NOT NULL
    begin
        drop table #Tmp2;
    end
    select convert(nvarchar,(convert(xml,VarValueXml)).query('/ProductItem[1]/Style[1]/text()[1]')) as StyleNO,
            Convert(int,Convert(nvarchar,(convert(xml,VarValueXml)).query('/ProductItem[1]/ProduceQty[1]/text()[1]'))) as ProduceQty
        into #Tmp2 from dbo.Tbl_ProduceOrderProductItem where OrderNO=@ORDERNO
    select @TotalProduceQtySUM=sum(ProduceQty) from #Tmp2;
    set @StyleNOSUM='/';
    select @StyleNOSUM=@StyleNOSUM+StyleNO+'/' from #Tmp2 Group BY StyleNO;    
---如果是分批出货，则数量重新计算
    if(@ReleaseNO!=-1)
    begin
        select @TotalProduceQtySUM=SUM(RealShipQty) from dbo.Tbl_ProduceRelease where ProduceNO = @ProduceNO and DeliveryAddrID = @DeliveryAddrID
    end
    if(@ReleaseNO!=-1)
    begin
        SELECT DISTINCT 
        @ShippingNO=Tbl_ShipOrderDetail.ShippingNO, 
        @ShipToType=Tbl_ShipOrder.DeliveryType, 
        @ShipToPayType=Tbl_ShipOrder.DeliveryFeeType, 
        @ShipToExpressName=Tbl_ShipOrder.ExpressCompany, 
        @ShipToExpressNO=Tbl_ShipOrder.ExpressNO, 
        @ShipDate=Tbl_ShipOrder.ShippingDate
        FROM         Tbl_ShipOrderDetail INNER JOIN
                              Tbl_ShipOrder ON Tbl_ShipOrderDetail.ShippingNO = Tbl_ShipOrder.ShippingNO
        where Tbl_ShipOrderDetail.OrderNO=@ORDERNO AND (Tbl_ShipOrderDetail.ProduceNO = @ProduceNO) AND (Tbl_ShipOrderDetail.ReleaseNO = @ReleaseNO)
    end
    else
    begin
        SELECT DISTINCT 
        @ShippingNO=Tbl_ShipOrderDetail.ShippingNO, 
        @ShipToType=Tbl_ShipOrder.DeliveryType, 
        @ShipToPayType=Tbl_ShipOrder.DeliveryFeeType, 
        @ShipToExpressName=Tbl_ShipOrder.ExpressCompany, 
        @ShipToExpressNO=Tbl_ShipOrder.ExpressNO, 
        @ShipDate=Tbl_ShipOrder.ShippingDate
        FROM         Tbl_ShipOrderDetail INNER JOIN
                              Tbl_ShipOrder ON Tbl_ShipOrderDetail.ShippingNO = Tbl_ShipOrder.ShippingNO
        where Tbl_ShipOrderDetail.OrderNO=@ORDERNO AND (Tbl_ShipOrderDetail.ProduceNO = @ProduceNO)
    end

--如果这个分批出货的工单没有出货，则更改这一笔记录的状态
if (@ShippingNO='') and (@ReleaseNO!=-1)
begin
    set @SysStatusID=160
end

SELECT   @OrderSysStatus=Tbl_RoleStatus.RoleStatusCn
FROM         Tbl_RoleStatus_SysStatus INNER JOIN
                      Tbl_RoleStatus ON Tbl_RoleStatus_SysStatus.RoleStatusID = Tbl_RoleStatus.RoleStatusID
WHERE     (Tbl_RoleStatus.RoleID = @PRoleID) AND Tbl_RoleStatus_SysStatus.SysStatusID=@SysStatusID

if (@CustConfirmDate IS NOT NULL) and (@SysStatusID=160)
begin
    set @OrderSysStatus=N'待出货'
end

    insert into #Tmp
(
ERPSONO,
OrderNO,
ProduceNO,
StyleNOs,
TotalProduceQty,
OrderTime,
CustomerNO,
CustomerName,
OrderStatus,
ShipToCmpName,
ShipToAddr,
ShipToName,
ShipToTel,
ShippingNO,
ShipToType ,
ShipToPayType,
ShipToExpressName,
ShipToExpressNO ,
ShipDate
)
values
(
@ERPSONO,
@ORDERNO,
@ProduceNO,
@StyleNOSUM,
@TotalProduceQtySUM,
@CreateTime,
@CustomerNO,
@CustomerName,
@OrderSysStatus,
@ShipCmpName,
@ShipAddress,
@ShipContactName,
@ShipContactTel,
@ShippingNO,
@ShipToType,
@ShipToPayType,
@ShipToExpressName,
@ShipToExpressNO,
@ShipDate
)
--变量值复位
set @ERPSONO='';
set @ORDERNO='';
set @ProduceNO='';
set @StyleNOSUM='';
set @TotalProduceQtySUM='';
set @CreateTime=NULL;
set @CustomerNO='';
set @CustomerName='';
set @OrderSysStatus='';
set @ShipCmpName='';
set @ShipAddress='';
set @ShipContactName='';
set @ShipContactTel='';
set @ShippingNO='';
set @ShipToType='';
set @ShipToPayType='';
set @ShipToExpressName='';
set @ShipToExpressNO='';
set @ShipDate=NULL;

 FETCH NEXT FROM contact_cursor INTO @ORDERNO,@CustomerNO,@CustomerName,@CustOrderNO,@ERPSONO,@ShipCmpNO,@ShipCmpName,@ShipAddress,@ShipContactName,@ShipContactTel,@SysStatusID,@CreateTime,@ProduceNO,@ReleaseNO,@DeliveryAddrID,@CustConfirmDate
END
--@PRoleID int 
--@PCustomerNO
--@PERPSO
--@PStyleNO
--@POrderTimeStart
--@POrderTimeEnd
declare @condition nvarchar(max);
set @condition='where 1=1 '
if(@PCustomerNO!='')
begin
    set @condition= @condition + ' AND CustomerNO='''+@PCustomerNO+''''
end
if(@PERPSO!='')
begin
    set @condition= @condition + ' AND ERPSONO='''+@PERPSO+''''
end
if(@PStyleNO!='')
begin
    set @condition= @condition + ' AND StyleNOs like ''%/'+@PStyleNO+'/%'''
end
set @condition= @condition + ' AND OrderTime >='''+ CONVERT(nvarchar(50),@POrderTimeStart,101) +''' and OrderTime<'''+ CONVERT(nvarchar(50),@POrderTimeEnd,101) + ''''
exec('select * from #Tmp '+ @condition )
END