存储过程里SQL语句执行的问题

[ldwlqy]

DECLARE @SQLCond varchar(500)

Set @SQLCond='Select UnitID From tblUnits Where ParentID=1234'

If EXISTS (Select UnitID From tblUnits Where ParentID In (@SQLCond) And UnitID<>ParentID)
print 'OK'
else
print 'No'


存储过程里Select语句能不能这样写？变量@SQLCond加进去整个语句执行会有什么问题吗？

[joncy88]

DECLARE @SQLCond varchar(500)

Set @SQLCond='Select UnitID From tblUnits Where ParentID=1234'

set @SQLCond = 'if exists(select UnitID From tblUnits Where ParentID In ' + '(' + @SQLCond + ')'
+ 'and UnitID<>ParentID)' + ' print ' + ''''+'OK' + '''' + ' else print ' + '''' + 'NO' +''''
exec (@SQLCond)

[ldwlqy]

gaobochina(皮皮)

不行
你试过了吗？
我这结果是
No

[DJMPH]

应该不行吧，@SQLCond没有执行啊。
动态语句要靠exec来执行的:exec (@SQLCond)
In (@SQLCond)这样恐怕不行。

[newdongkui]

不行
你想实现什么

[angle1219]

不行

[glboy]

建议将
Select UnitID From tblUnits Where ParentID In (@SQLCond) And UnitID<>ParentID整个放入一@str_sqlexecute变量中，然后exec(str_sql)

大概：
declare @Count int//作为计数
declare @str_sqlexecute varchar(500)//执行的
@str_sqlexecute = select @Count=count(UnitID) from....（你的条件）
exec(@str_sqlexecute )

if @Count = 0
begin
....
end

else
...
希望能对你有帮助，应该是可以达到你的要求的

[ldwlqy]

glboy(星毅)

@str_sqlexecute = select @Count=count(UnitID) from....（你的条件）
exec(@str_sqlexecute )

改为
@str_sqlexecute = 'select @Count=count(UnitID) from....'
exec(@str_sqlexecute )

但执行时报错
该怎样写呢？

[chpeng]

先将exec（@SQLCond）插入临时表，
再执行下面语句

[CrazyFor]

TRY:
Set @SQLCond='Select UnitID From tblUnits Where ParentID=1234'

exec('If EXISTS (Select UnitID From tblUnits Where ParentID In ('+@SQLCond+') And UnitID<>ParentID)
print 'OK'
else
print 'No'
')

[ldwlqy]

改为
EXECUTE sp_executesql @SQLCond

报错
必须声明变量 '@IDCount'

[ldwlqy]

CrazyFor(蚂蚁)

Set @SQLCond='Select UnitID From tblUnits Where ParentID=1234'

exec('If EXISTS (Select UnitID From tblUnits Where ParentID In ('+@SQLCond+') And UnitID<>ParentID)
print 'OK'
else
print 'No'

这样可以
但是我判断后还有从新给变量 @SQLCond 赋值
set @SQLCond=......
语句该怎样写呢？

[gaobochina]

insert #tblunits
values(1,'1234')

DECLARE @SQLCond varchar(500)

Set @SQLCond='Select UnitID From #tblUnits Where ParentID=1234'
--此举应为 select @SQLCond=UnitID From #tblUnits Where ParentID=1234'
select @SQLCond
If EXISTS (Select UnitID From #tblUnits Where ParentID In (@SQLCond) And UnitID<>ParentID)
print 'OK'
else
print 'No'
drop table #tblUnits

[gaobochina]

create table #tblUnits
(
unitid varchar(10),
parentID varchar(10)

)

insert #tblunits
values(1,'1234')

DECLARE @SQLCond varchar(500)

Set @SQLCond='Select UnitID From #tblUnits Where ParentID=1234'

--select @SQLCond=UnitID From #tblUnits WHere ParentID='1234'
select @SQLCond
If EXISTS (Select UnitID From #tblUnits Where ParentID In (@SQLCond) And UnitID<>ParentID)
print 'OK'
else
print 'No'

drop table #tblUnits