declare @sqlstr varchar(8000) set @sqlstr = 'insert into Exam_Users_Sub(EUS_UserID, EUS_EG_ID, EUS_Q_ID) select top '+cast(@qCount_MN as varchar)+' '+cast(@UserID as varchar)+' as UserID,'+cast(@EG_id as varchar)+' as EG_id,Q_id from Questions where Q_C_ID = '+cast(@C_id as varchar)+' and Q_Type ='+ cast(@pCount_temp as varchar)+' nd Q_Degree = 2 order by newid()' exec(@sqlstr)
放在外部的话,你可以print @sql看看串的值,不是你希望的SQL语句。应该放在内部。
' nd Q_Degree = 2 order by '+cast(newid() as varchar(50))+'' --------->掉了个'a' ' and Q_Degree = 2 order by '+cast(newid() as varchar(50))+''
' and Q_Degree = 2 order by newid()'
use pubs godeclare @str varchar(1000) declare @k int set @k=3 set @str=' select top '+ rtrim(@k) +' * from jobs order by newid()' --print @str exec(@str)job_id job_desc min_lvl max_lvl ------ -------------------------------------------------- ------- ------- 7 Marketing Manager 120 200 14 Designer 25 100 5 Publisher 150 250
解决问题,字串内部的错误没检查,仅仅是将 order by newid() 放在字串里面。楼上已经指出 nd 附近错误,应该是 and
不行,把newid()放在内部,用查询分析器调用的时候,其实为无效的标示字段
放在内部没有问题,如:declare @sql varchar(8000),@i intset @i=100set @sql = 'select top ' + cast(@i as varchar(100)) + ' * from sysobjects order by newid()'print @sqlexec (@sql)
set @sqlstr = 'insert into Exam_Users_Sub(EUS_UserID, EUS_EG_ID, EUS_Q_ID) select top '+cast(@qCount_MN as varchar)+' '+cast(@UserID as varchar)+' as UserID,'+cast(@EG_id as varchar)+' as EG_id,Q_id from Questions where Q_C_ID = '+cast(@C_id as varchar)+' and Q_Type ='+ cast(@pCount_temp as varchar)+' nd Q_Degree = 2 order by newid()'
exec(@sqlstr)
--------->掉了个'a'
' and Q_Degree = 2 order by '+cast(newid() as varchar(50))+''
use pubs
godeclare @str varchar(1000)
declare @k int
set @k=3
set @str='
select top '+ rtrim(@k) +' * from jobs order by newid()'
--print @str
exec(@str)job_id job_desc min_lvl max_lvl
------ -------------------------------------------------- ------- -------
7 Marketing Manager 120 200
14 Designer 25 100
5 Publisher 150 250