TO:ojuju10 我说的@name在存储过程里是@ship_date。 比较长,我先贴一半吧,全部一次放不下。不够再加。CREATE PROCEDURE usp_get_status_delivery @ship_date datetime, @function_id int
AS SET NOCOUNT ON declare @quantity int, @item_cd nvarchar(15), @sha_seq int, @area_cd char(10), @c_num char(7), @custname nvarchar(100), @address nvarchar(200), @home_tel nvarchar(20), @jpn_zip nvarchar(20), @seq int, @c_no int --如果#delivery_addr里存在数据,则 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#delivery_addr]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#delivery_addr] --删除临时表#delivery_addr后再创建 create table #delivery_addr( addr_id int IDENTITY (1, 1), c_no int, area_cd char(10), c_num char(7), custname nvarchar(100), item_cd nvarchar(15), quantity int, weddingdate varchar(10), sha_seq int, address nvarchar(200), jpn_zip nvarchar(20), home_tel nvarchar(20), ship_date datetime, sendto_shop int, print_value nvarchar(20)) --建立一个游标delivery DECLARE delivery CURSOR FOR --取数据,从sha_delivery_addr表里 --条件:item_cd不在表item-mini,ship_date=入参@ship_date,area_cd不等于WS,sha_seq和seq不在sha_shipment里的。 -- groupby所有字段,orderby item_cd,area_cd,c_num,c_no select sum(quantity) as quantity ,c_no,item_cd,sha_seq,area_cd,convert(char(7),c_num) as c_num,isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,'') as custname,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'')as address ,home_tel,jpn_zip,ship_date from sha_delivery_addr where item_cd not in(select distinct(item_mini) from item_mini) and ship_date=@ship_date and area_cd <> 'WS' and cxl=0 and convert(varchar,sha_seq)+convert(varchar,seq) not in(select convert(varchar,sha_seq)+convert(varchar,seq) from sha_shipment) group by c_no,sha_seq,area_cd,item_cd,c_num ,isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,'') ,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'') ,home_tel,jpn_zip,ship_date order by item_cd,area_cd,c_num,c_no OPEN delivery FETCH NEXT FROM delivery INTO @quantity, @c_no,@item_cd ,@sha_seq, @area_cd,@c_num,@custname,@address,@home_tel,@jpn_zip,@ship_date WHILE @@FETCH_STATUS = 0 BEGIN --往临时表#delivery_addr里插入数据,数据源:变量@XXX系列。@ship_date的值为A的值。 insert into #delivery_addr(c_no,area_cd,c_num,custname,item_cd,quantity,sha_seq,address,jpn_zip ,home_tel, ship_date) values(@c_no,@area_cd,convert(char(7),@c_num), isnull(@custname,''),@item_cd,@quantity,@sha_seq,isnull(@address,''),isnull(@jpn_zip,'') ,isnull(@home_tel,''),@ship_date) if exists(select * from sha_delivery_addr where area_cd=@area_cd and c_num=@c_num and item_cd in(select distinct(item_mini) from item_mini where item_cd=@item_cd) and ship_date=@ship_date and convert(varchar,sha_seq)+convert(varchar,seq) not in(select convert(varchar,sha_seq)+convert(varchar,seq) from sha_shipment)) begin insert into #delivery_addr(c_no,area_cd,c_num,custname,item_cd,quantity ,sha_seq,address,jpn_zip ,home_tel, ship_date) select c_no,area_cd,c_num, isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,'') ,item_cd,sum(quantity), sha_seq,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,''),jpn_zip,home_tel,ship_date from sha_delivery_addr where area_cd=@area_cd and c_num=@c_num and item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date group by c_no ,sha_seq,area_cd,item_cd,c_num , isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,''),isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,''),home_tel,jpn_zip,ship_date end FETCH NEXT FROM delivery INTO @quantity, @c_no,@item_cd ,@sha_seq, @area_cd,@c_num,@custname,@address,@home_tel,@jpn_zip,@ship_date END CLOSE delivery DEALLOCATE deliveryif exists (select * from sha_delivery_addr a where item_cd in(select distinct(item_mini) from item_mini) and a.ship_date=@ship_date and convert(varchar,sha_seq)+convert(varchar,seq) not in(select convert(varchar,sha_seq)+convert(varchar,seq) from sha_shipment) and convert(varchar,a.sha_seq)+a.c_num not in(select convert(varchar,sha_seq)+c_num from #delivery_addr where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date) ) -- 创建游标delivery1 begin DECLARE delivery1 CURSOR FOR select sum(quantity) as quantity ,c_no,item_cd,sha_seq,seq,area_cd,c_num,isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,'') as custname,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'') as address ,home_tel,jpn_zip,ship_date from sha_delivery_addr a where item_cd in(select distinct(item_mini) from item_mini) and a.ship_date=@ship_date and convert(varchar,sha_seq)+convert(varchar,seq) not in(select convert(varchar,sha_seq)+convert(varchar,seq) from sha_shipment) and convert(varchar,a.sha_seq)+a.c_num not in(select convert(varchar,sha_seq)+c_num from #delivery_addr where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date) group by seq,c_no,sha_seq,area_cd,item_cd,c_num ,isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,''),isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'') ,home_tel,jpn_zip,ship_date --打开游标 OPEN delivery1--C.取第一行值给变量@XXX系列,@ship_date在这里的值变了。 FETCH NEXT FROM delivery1 INTO @quantity, @c_no,@item_cd ,@sha_seq, @seq,@area_cd,@c_num,@custname,@address,@home_tel,@jpn_zip,@ship_date --移动游标,其它所有行更新操作(当到结尾时退出) WHILE @@FETCH_STATUS = 0 BEGIN --如果(SQL)存在 if exists(select * from #delivery_addr where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date and sha_seq=@sha_seq and area_cd=@area_cd and c_num=@c_num) begin --更新#delivery_addr,让quantity+1 --条件:item_cd在item_mini(M_Item_Mini)表item_mini字段的数据里 -- ship_date,sha_seq,area_cd,c_num与上面的参数相等 update #delivery_addr set quantity=quantity+1 where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date and sha_seq=@sha_seq and area_cd=@area_cd and c_num=@c_num end else begin --否则,插入#delivery_addr,来源:sha_delivery_addr --条件:item_cd在item_mini(M_Item_Mini)表item_mini字段的数据里 -- ship_date=C的@ship_date -- sha_seq=@sha_seq ,area_cd=@area_cd , c_num=@c_num , seq=@seq --再group by一下 insert into #delivery_addr(c_no,area_cd,c_num,custname,item_cd,quantity ,sha_seq,address,jpn_zip ,home_tel, ship_date) select c_no,area_cd,c_num, isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,''),item_cd,sum(quantity), sha_seq,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'') ,jpn_zip,home_tel,ship_date from sha_delivery_addr where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date and sha_seq=@sha_seq and area_cd=@area_cd and c_num=@c_num and seq=@seq group by c_no ,sha_seq,area_cd,item_cd,c_num , isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,''),isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,''),home_tel,jpn_zip,ship_date end --D.取游标下一行的值,@ship_date又变了! FETCH NEXT FROM delivery1 INTO @quantity, @c_no,@item_cd ,@sha_seq,@seq, @area_cd,@c_num,@custname,@address,@home_tel,@jpn_zip,@ship_date END CLOSE delivery1 DEALLOCATE delivery1
我说的@name在存储过程里是@ship_date。
比较长,我先贴一半吧,全部一次放不下。不够再加。CREATE PROCEDURE usp_get_status_delivery @ship_date datetime,
@function_id int
AS
SET NOCOUNT ON
declare @quantity int,
@item_cd nvarchar(15),
@sha_seq int,
@area_cd char(10),
@c_num char(7),
@custname nvarchar(100),
@address nvarchar(200),
@home_tel nvarchar(20),
@jpn_zip nvarchar(20),
@seq int,
@c_no int
--如果#delivery_addr里存在数据,则
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#delivery_addr]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#delivery_addr]
--删除临时表#delivery_addr后再创建
create table #delivery_addr(
addr_id int IDENTITY (1, 1),
c_no int,
area_cd char(10),
c_num char(7),
custname nvarchar(100),
item_cd nvarchar(15),
quantity int,
weddingdate varchar(10),
sha_seq int,
address nvarchar(200),
jpn_zip nvarchar(20),
home_tel nvarchar(20),
ship_date datetime,
sendto_shop int,
print_value nvarchar(20))
--建立一个游标delivery
DECLARE delivery CURSOR FOR
--取数据,从sha_delivery_addr表里
--条件:item_cd不在表item-mini,ship_date=入参@ship_date,area_cd不等于WS,sha_seq和seq不在sha_shipment里的。
-- groupby所有字段,orderby item_cd,area_cd,c_num,c_no select sum(quantity) as quantity ,c_no,item_cd,sha_seq,area_cd,convert(char(7),c_num) as c_num,isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,'') as custname,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'')as address
,home_tel,jpn_zip,ship_date
from sha_delivery_addr
where item_cd not in(select distinct(item_mini) from item_mini)
and ship_date=@ship_date and area_cd <> 'WS'
and cxl=0
and convert(varchar,sha_seq)+convert(varchar,seq) not in(select convert(varchar,sha_seq)+convert(varchar,seq) from sha_shipment)
group by c_no,sha_seq,area_cd,item_cd,c_num ,isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,'') ,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'')
,home_tel,jpn_zip,ship_date
order by item_cd,area_cd,c_num,c_no
OPEN delivery
FETCH NEXT
FROM delivery
INTO @quantity, @c_no,@item_cd ,@sha_seq, @area_cd,@c_num,@custname,@address,@home_tel,@jpn_zip,@ship_date
WHILE @@FETCH_STATUS = 0
BEGIN
--往临时表#delivery_addr里插入数据,数据源:变量@XXX系列。@ship_date的值为A的值。
insert into #delivery_addr(c_no,area_cd,c_num,custname,item_cd,quantity,sha_seq,address,jpn_zip ,home_tel,
ship_date) values(@c_no,@area_cd,convert(char(7),@c_num),
isnull(@custname,''),@item_cd,@quantity,@sha_seq,isnull(@address,''),isnull(@jpn_zip,'')
,isnull(@home_tel,''),@ship_date) if exists(select * from sha_delivery_addr where area_cd=@area_cd and c_num=@c_num and item_cd in(select distinct(item_mini) from item_mini where item_cd=@item_cd) and ship_date=@ship_date
and convert(varchar,sha_seq)+convert(varchar,seq) not in(select convert(varchar,sha_seq)+convert(varchar,seq) from sha_shipment))
begin
insert into #delivery_addr(c_no,area_cd,c_num,custname,item_cd,quantity ,sha_seq,address,jpn_zip ,home_tel,
ship_date) select c_no,area_cd,c_num, isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,'') ,item_cd,sum(quantity),
sha_seq,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,''),jpn_zip,home_tel,ship_date from sha_delivery_addr where
area_cd=@area_cd and c_num=@c_num and item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date group by c_no ,sha_seq,area_cd,item_cd,c_num ,
isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,''),isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,''),home_tel,jpn_zip,ship_date
end FETCH NEXT
FROM delivery
INTO @quantity, @c_no,@item_cd ,@sha_seq, @area_cd,@c_num,@custname,@address,@home_tel,@jpn_zip,@ship_date
END
CLOSE delivery
DEALLOCATE deliveryif exists (select * from sha_delivery_addr a where item_cd in(select distinct(item_mini) from item_mini) and a.ship_date=@ship_date
and convert(varchar,sha_seq)+convert(varchar,seq) not in(select convert(varchar,sha_seq)+convert(varchar,seq) from sha_shipment)
and convert(varchar,a.sha_seq)+a.c_num
not in(select convert(varchar,sha_seq)+c_num from #delivery_addr where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date) )
-- 创建游标delivery1
begin
DECLARE delivery1 CURSOR FOR
select sum(quantity) as quantity ,c_no,item_cd,sha_seq,seq,area_cd,c_num,isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,'') as custname,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'') as address
,home_tel,jpn_zip,ship_date from sha_delivery_addr a where item_cd in(select distinct(item_mini) from item_mini) and a.ship_date=@ship_date and
convert(varchar,sha_seq)+convert(varchar,seq) not in(select convert(varchar,sha_seq)+convert(varchar,seq) from sha_shipment) and convert(varchar,a.sha_seq)+a.c_num
not in(select convert(varchar,sha_seq)+c_num from #delivery_addr where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date)
group by seq,c_no,sha_seq,area_cd,item_cd,c_num ,isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,''),isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'')
,home_tel,jpn_zip,ship_date
--打开游标
OPEN delivery1--C.取第一行值给变量@XXX系列,@ship_date在这里的值变了。
FETCH NEXT
FROM delivery1
INTO @quantity, @c_no,@item_cd ,@sha_seq, @seq,@area_cd,@c_num,@custname,@address,@home_tel,@jpn_zip,@ship_date
--移动游标,其它所有行更新操作(当到结尾时退出)
WHILE @@FETCH_STATUS = 0
BEGIN
--如果(SQL)存在
if exists(select * from #delivery_addr where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date and sha_seq=@sha_seq and area_cd=@area_cd and c_num=@c_num)
begin
--更新#delivery_addr,让quantity+1
--条件:item_cd在item_mini(M_Item_Mini)表item_mini字段的数据里
-- ship_date,sha_seq,area_cd,c_num与上面的参数相等
update #delivery_addr set quantity=quantity+1 where item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date and sha_seq=@sha_seq and area_cd=@area_cd and c_num=@c_num
end
else
begin
--否则,插入#delivery_addr,来源:sha_delivery_addr
--条件:item_cd在item_mini(M_Item_Mini)表item_mini字段的数据里
-- ship_date=C的@ship_date
-- sha_seq=@sha_seq ,area_cd=@area_cd , c_num=@c_num , seq=@seq
--再group by一下
insert into #delivery_addr(c_no,area_cd,c_num,custname,item_cd,quantity ,sha_seq,address,jpn_zip ,home_tel,
ship_date) select c_no,area_cd,c_num, isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,''),item_cd,sum(quantity),
sha_seq,isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,'') ,jpn_zip,home_tel,ship_date from sha_delivery_addr where
item_cd in(select distinct(item_mini) from item_mini) and ship_date=@ship_date and sha_seq=@sha_seq and area_cd=@area_cd and c_num=@c_num and seq=@seq
group by c_no ,sha_seq,area_cd,item_cd,c_num ,
isnull(d_last_kanji,'')+' '+isnull(d_first_kanji,''),isnull(addr_kanji1,'')+isnull(addr_kanji2,'')+isnull(addr_kanji3 ,''),home_tel,jpn_zip,ship_date
end
--D.取游标下一行的值,@ship_date又变了!
FETCH NEXT
FROM delivery1
INTO @quantity, @c_no,@item_cd ,@sha_seq,@seq, @area_cd,@c_num,@custname,@address,@home_tel,@jpn_zip,@ship_date
END
CLOSE delivery1
DEALLOCATE delivery1
-----------------
b,因为@name被赋值为b,第二个游标调用时,@name的值为b-----------------------------------------------------
在我这里,@ship_date是在被FETCH NEXT循环赋值,那实际上第一个游标结束的时候,@ship_date就是第一个游标的数据集里的最后一行的数据了??