关于动态执行语句与newid()的问题
我要做一条数据筛选语句,随机抽出N条数据,这个N是不定的变量值,必须使用动态执行语句
代码如下:
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   '+cast(newid()   as   varchar(50))+ ' '
exec   @sqlstr

这样的话,newid()这个函数就不能起作用,而且语句不能正常执行,有没有人碰到过类似的问题?谢谢

------解决方案--------------------
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)

------解决方案--------------------
' nd Q_Degree = 2 order by '+cast(newid() as varchar(50))+ ' '
---------> 掉了个 'a '
' and Q_Degree = 2 order by '+cast(newid() as varchar(50))+ ' '

------解决方案--------------------

use pubs
go

declare @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

------解决方案--------------------
放在内部没有问题，如：


declare @sql varchar(8000),@i int

set @i=100

set @sql = 'select top ' + cast(@i as varchar(100)) + ' * from sysobjects order by newid() '

print @sql

exec (@sql)