ALTER PROCEDURE sp_sessionseatType_lf AS
begin
--lf 20161117
--分析场次中定义票种类型和实际出票类型差异 并且输出备注信息
declare --声明变量
@cSessionSeatTypeID varchar(50),--声明座位类型ID列
@SeatPriceTypeName varchar(50), --声明座位名称列
@sumtype int,
--声明座位类型汇总列
@command varchar(1000),
--备注列 (用来输出或者打印信息)
@PriceTypeName varchar(50),
@ticketCount int,
@order_count int
--外层游标
declare cursor_session cursor for
select
cSessionSeatTypeID ,SeatPriceTypeName,COUNT(*) as sumtype
from [sessionxxxx]
LEFT JOIN [SessionSeatType] ON SeatPriceTypeSessionId =xxxx AND cSessionSeatTypeID=SeatPriceTypeId
where cSessionOrderId!=0 group by cSessionSeatTypeID,SeatPriceTypeName
--打开游标
open cursor_session
--获取游标的下一行数据
fetch next from cursor_session into @cSessionSeatTypeID, @SeatPriceTypeName,@sumtype
--FETCH语句执行成功
while @@fetch_status = 0
begin
--set @command ='-'+cast(@cSessionSeatTypeID as varchar(10)) + '-'+@SeatPriceTypeName+'-'+ cast(@sumtype as varchar(10))
set @command =@SeatPriceTypeName+' 定义数 '+ cast(@sumtype as varchar(10))
--声明内层游标
declare cursor_order cursor for
select SST.SeatPriceTypeName as PriceTypeName,sum(O.ticketCount ) as ticketCount from [Order] O
LEFT JOIN [SessionSeatType] SST ON SST.SeatPriceTypeSessionId =xxxx AND O.orderSpecialPfid=SST.SeatPriceTypeId
where O.orderId in (
select cSessionOrderId from [Sessionxxxx] where cSessionSeatTypeID =@cSessionSeatTypeID and cSessionOrderId !=0
)group by SST.SeatPriceTypeName
--打开内层游标
open cursor_order
--获取内层游标的下一行数据
fetch next from cursor_order into @PriceTypeName, @ticketCount
set @order_count=0
while @@fetch_status = 0
begin
if(@order_count=0)
begin
set @command +=' 打印信息:'+@PriceTypeName+' '+cast(@ticketCount as varchar(10))+'张 '
set @order_count=1
end
else
begin
set @command +=@PriceTypeName+' '+cast(@ticketCount as varchar(10))+'张 '
end
fetch next from cursor_order into @PriceTypeName, @ticketCount
end
--关闭内层游标
close cursor_order
--释放内层游标
deallocate cursor_order
set @order_count=0
print @command
set @command=''
--EXEC (@command)
fetch next from cursor_session into @cSessionSeatTypeID, @SeatPriceTypeName,@sumtype
end
--关闭外层游标
close cursor_session
--释放外层游标
deallocate cursor_session
end
GO
--Exec sp_sessionseatType_lf