Getting values from dynamic SQL

[Tahir]

I am looking for a way to retrieve column values from dynamic SQL. I have
to use dynamic SQL because my tablenames are stored in a catalog table. Here's
the code snippet!

select @str = 'Select userid from ' + @tblName + ' where id=2'
exec(@str)

I want to retrieve userid from this dynamic sql to execute more sql statements
without going back to client script.
Select @userid=userid wont work because of variable scope. I can use cursors
but it is definitely more overhead.

Thanks.

[DaveSatz]

You need temp tables:

create table #x
( userid sysname )

select @str = 'INSERT #x Select userid from ' + @tblName + ' where id=2'
exec(@str)

Select @userid=userid
from #x

--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
(using VB6 SP3/MTS/SQL Server 6.5 SP5a)
(Please respond to the newsgroup.)


Tahir <tsawan@usa.net> wrote in message news:38d9395a$1@news.devx.com...
>
> I am looking for a way to retrieve column values from dynamic SQL. I have
> to use dynamic SQL because my tablenames are stored in a catalog table.
Here's
> the code snippet!
>
> select @str = 'Select userid from ' + @tblName + ' where id=2'
> exec(@str)
>
> I want to retrieve userid from this dynamic sql to execute more sql
statements
> without going back to client script.
> Select @userid=userid wont work because of variable scope. I can use
cursors
> but it is definitely more overhead.
>
> Thanks.