Create proc up_getSumball
@XmlString varchar(2000),
@Max int
as
begin
declare @idtb table(id int)
create table #tb (id int,num int)
declare @num int,@id int,@sum int,@times int
declare @idHandle int
set @sum=0
set @times=0
EXEC sp_xml_preparedocument @idHandle OUTPUT, @XmlString
insert into #tb(id,num)
select * from openxml(@idHandle,N'/root/tb_ball')
with #tb
while (@sum <>@max or @times <>6)
begin
select top 1 @id=id,@num=num from #tb where num <=33 order by newid()
if @num <>@max begin
if not exists(select 1 from @idtb where id=@id)
insert into @idtb select @id
end
select @sum=sum(num) from #tb where id in(select id from @idtb)
select @times=count(1) from @idtb
if (@times>6 ) begin
delete @idtb
end
if ((@Max=@num) and (@times <6)) begin
delete @idtb
end
select @times=count(1) from @idtb
end
select * from #tb where id in(select id from @idtb)
drop table #tb
end
go
declare @tb table(id int,num int)
insert into @tb select 1,1
insert into @tb select 2,2
insert into @tb select 3,3
insert into @tb select 4,4
insert into @tb select 5,5
insert into @tb select 6,6
insert into @tb select 7,7
insert into @tb select 8,8
insert into @tb select 9,9
insert into @tb select 11,11
insert into @tb select 12,12
insert into @tb select 13,13
insert into @tb select 14,14
insert into @tb select 15,15
insert into @tb select 16,16
insert into @tb select 17,17
insert into @tb select 18,18
insert into @tb select 19,19
insert into @tb select 20,20
insert into @tb select 21,21
insert into @tb select 22,22
insert into @tb select 23,23
insert into @tb select 24,24
insert into @tb select 25,25
insert into @tb select 26,26
insert into @tb select 27,27
insert into @tb select 28,28
insert into @tb select 29,29
insert into @tb select 30,30
insert into @tb select 31,31
insert into @tb select 32,32
insert into @tb select 33,33
declare @dataxml xml
declare @strXml varchar(2000)
set @dataxml=(select * from @tb as tb_ball for xml auto,root('root'))
set @strXml=convert(varchar(2000),@dataxml)
exec up_getSumball @strXml,50
|