@SingleNum int @MultiNum int 这两个变量的类型与newid()产生的不匹配
用動態的sql吧,top後面是不可能接變量的。
select newid() as ni,* into #t from Exam_tm select * from #t a,( select tm_type='单选题',@SingleNum as counts union all select tm_type='多选题',@MultiNum as counts ) b where a.tm_type=b.tmtype and (select count(*) from #t where tm_type=a.tm_type and ni<=a.ni)<=b.counts
declare @y int set @y=1 --select top @y * from test1 declare @vsql varchar(800) set @vsql='select top '+convert(char, @y)+'* from test1' exec(@vsql) 給段例子,呵呵
谢谢各位,问题解决.CREATE PROCEDURE GetRandomExam_tmByCondition @ItemID int, @SingleNum int, @MultiNum int AS declare @y int declare @z int set @y=@SingleNum set @z=@MultiNum declare @vsql varchar(800) set @vsql='select * from( select * from( select top '+convert(varchar, @y)+' aa=newid(),* from Exam_tm where tm_type='+'''单选题'''+' order by aa )a union all select * from( select top '+convert(varchar,@z)+' aa=newid(),* from Exam_tm where tm_type='+'''多选题'''+' order by aa )b )a where tkjchildid='+convert(varchar,@ItemID)exec(@vsql)
@MultiNum int
这两个变量的类型与newid()产生的不匹配
from #t a,( select tm_type='单选题',@SingleNum as counts union all
select tm_type='多选题',@MultiNum as counts ) b
where a.tm_type=b.tmtype
and (select count(*) from #t where tm_type=a.tm_type and ni<=a.ni)<=b.counts
set @y=1
--select top @y * from test1
declare @vsql varchar(800)
set @vsql='select top '+convert(char, @y)+'* from test1'
exec(@vsql)
給段例子,呵呵
@ItemID int,
@SingleNum int,
@MultiNum int
AS
declare @y int
declare @z int
set @y=@SingleNum
set @z=@MultiNum
declare @vsql varchar(800)
set @vsql='select *
from(
select * from(
select top '+convert(varchar, @y)+' aa=newid(),* from Exam_tm where tm_type='+'''单选题'''+' order by aa
)a
union all
select * from(
select top '+convert(varchar,@z)+' aa=newid(),* from Exam_tm
where tm_type='+'''多选题'''+' order by aa
)b
)a where tkjchildid='+convert(varchar,@ItemID)exec(@vsql)