select exec @String_Variable

Question

Post reply

select exec @String_Variable

AVB

SSChampion

Points: 11418
More actions
April 3, 2006 at 3:02 pm

#190066

I have a string_variable that I have written
declare @Foo varchar, @IDCount char
set @IdCount = 3
set @Foo = 'select top '+ @IDCount +' agent_id from t_1'
exec (@Foo)

How can I select from this string Variable in this statement?
select ID from t_Test where ID not in (select ID from exec (@Foo))
Can this be done? Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

SET ROWCOUNT @IDCount

select agent_id

INTO #Foo

from t_1'

SET RWCOUNT 0

select ID from t_Test where ID not in (select agent_id from #Foo)

_____________
Code for TallyGenerator

AVB SSChampion Points: 11418 More actions · Answer 2

Sergiy,

Thanks for the reply. I've been tooling around with this a little this morning. If I run that query more than once it tells me that the object #Foo already exists in the database. What do you refere to those "objects" as? and where are they stored in the DB? Thanks for your help!

Sergiy SSC Guru Points: 110208 More actions · Answer 3

# tables exist until connection is ended or you explicitely drop it.

If you run it from QA it will stay there until you close the window or execute

DROP #Foo.

If you use it inside SP it will be dropped automatically when procedure is finished.

See BOL topics about temp tables.

_____________
Code for TallyGenerator

AVB SSChampion Points: 11418 More actions · Answer 4

Thanks for the reply again. I was just reading about dropping it in QA to run it mulitple times. I'll check out the BOL as well. Thanks again.