How to access the result of the dynamic sql in sp

Hi,
In my sp I have a dynamic sql as
set @.qry = 'select v1, v2 from ' + @.table + ' where ...'
in this sp how can I user v1? I need to check the value of v1. ThanksHi Jen
You'll have to assign v1 to a parameter, which is just like assigning to a
variable as you were shown in your earlier post.
Then you'll have to use sp_executesql instead of EXEC ( ), and following the
suggestions in the KB article:
How to specify output parameters when you use the sp_executesql stored
procedure in SQL Server
http://support.microsoft.com/kb/262499/en-us
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:D1C3210C-9FDB-438E-85BF-6C212BB63915@.microsoft.com...
> Hi,
> In my sp I have a dynamic sql as
> set @.qry = 'select v1, v2 from ' + @.table + ' where ...'
> in this sp how can I user v1? I need to check the value of v1. Thanks|||Thanks. I use it in the same procedure like the following, why can't I
insert into table variable:
BEGIN
SET @.qry = 'SELECT v1, v1 FROM ' + @.tablename + ' WHERE .... '
-- next line failed
INSERT INTO @.t2 EXEC @.qry
if @.@.rowcount>0
BEGIN
SELECT @.type=v1 from @.t2
"Kalen Delaney" wrote:

> Hi Jen
> You'll have to assign v1 to a parameter, which is just like assigning to a
> variable as you were shown in your earlier post.
> Then you'll have to use sp_executesql instead of EXEC ( ), and following t
he
> suggestions in the KB article:
> How to specify output parameters when you use the sp_executesql stored
> procedure in SQL Server
> http://support.microsoft.com/kb/262499/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:D1C3210C-9FDB-438E-85BF-6C212BB63915@.microsoft.com...
>
>|||Did you read the responses yet to the other query about capturing a value
into a variable? That should be your starting point, and then read the KB
article. This solution below looks like you did neither.
You have assign to the variable in the first select. Also, in the first
select, why are you returning the same column twice?
Your second select makes no sense; FROM must be followed by a table name,
not a variable.
What does 'failed' mean? Did you get an error? What did it say?
What version are you running?
Can you tell us exactly what you are trying to do?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4CDDB3D0-D426-4BBA-BE4D-CF98655CAF3C@.microsoft.com...
> Thanks. I use it in the same procedure like the following, why can't I
> insert into table variable:
> BEGIN
> SET @.qry = 'SELECT v1, v1 FROM ' + @.tablename + ' WHERE .... '
> -- next line failed
> INSERT INTO @.t2 EXEC @.qry
> if @.@.rowcount>0
> BEGIN
> SELECT @.type=v1 from @.t2
> "Kalen Delaney" wrote:
>|||I got this error:
Server: Msg 197, Level 15, State 1, Line 37
EXECUTE cannot be used as a source when inserting into a table variable.
I tried to loop through a few tables ( the table names are populated into a
table variable @.t1), and find the specific value v1 from that table.
SELECT TOP 1 @.tablename=tableName FROM @.t1
WHILE( @.@.rowcount>0)
BEGIN
SET @.qry = 'SELECT v1,v2 FROM ' + @.tablename + ' WHERE .... '
INSERT INTO @.t2 EXEC @.qry
if @.@.rowcount>0
BEGIN
SELECT @.type=v1 from @.t2
"Kalen Delaney" wrote:

> Did you read the responses yet to the other query about capturing a value
> into a variable? That should be your starting point, and then read the KB
> article. This solution below looks like you did neither.
> You have assign to the variable in the first select. Also, in the first
> select, why are you returning the same column twice?
> Your second select makes no sense; FROM must be followed by a table name,
> not a variable.
> What does 'failed' mean? Did you get an error? What did it say?
> What version are you running?
> Can you tell us exactly what you are trying to do?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:4CDDB3D0-D426-4BBA-BE4D-CF98655CAF3C@.microsoft.com...
>
>|||Jen
There are too many problems here to list. Please tell us EXACTLY what you
are trying to accomplish.
Did you read my comments to this post?
Did you read the responses to your other post about capturing a value into a
variable?
Did you read the KB article?
Also read this article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:B32ECCBF-13AB-404E-8E2C-ACC07EE8CF21@.microsoft.com...
>I got this error:
> Server: Msg 197, Level 15, State 1, Line 37
> EXECUTE cannot be used as a source when inserting into a table variable.
> I tried to loop through a few tables ( the table names are populated into
> a
> table variable @.t1), and find the specific value v1 from that table.
> SELECT TOP 1 @.tablename=tableName FROM @.t1
> WHILE( @.@.rowcount>0)
> BEGIN
> SET @.qry = 'SELECT v1,v2 FROM ' + @.tablename + ' WHERE .... '
> INSERT INTO @.t2 EXEC @.qry
> if @.@.rowcount>0
> BEGIN
> SELECT @.type=v1 from @.t2
>
> "Kalen Delaney" wrote:
>|||Insert into table variable will not work for EXEC.
You need to use a temporary table.
Hope this helps.
"Jen" wrote:
> I got this error:
> Server: Msg 197, Level 15, State 1, Line 37
> EXECUTE cannot be used as a source when inserting into a table variable.
> I tried to loop through a few tables ( the table names are populated into
a
> table variable @.t1), and find the specific value v1 from that table.
> SELECT TOP 1 @.tablename=tableName FROM @.t1
> WHILE( @.@.rowcount>0)
> BEGIN
> SET @.qry = 'SELECT v1,v2 FROM ' + @.tablename + ' WHERE .... '
> INSERT INTO @.t2 EXEC @.qry
> if @.@.rowcount>0
> BEGIN
> SELECT @.type=v1 from @.t2
>
> "Kalen Delaney" wrote:
>|||Thanks, I read all. I read the response to other post, and I used it in my
first line "
SELECT TOP 1 @.tablename=tableName FROM @.t1 ". I read the KB too, but
sp_executesql' statement can't have variable, I have to use @.tablename
variable.
And I am using sql2000 sp3.
"Kalen Delaney" wrote:

> Jen
> There are too many problems here to list. Please tell us EXACTLY what you
> are trying to accomplish.
> Did you read my comments to this post?
> Did you read the responses to your other post about capturing a value into
a
> variable?
> Did you read the KB article?
> Also read this article:
> http://www.sommarskog.se/dynamic_sql.html
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com|||How can I get the v1 value without temporary table? Anyother way? What I am
trying to do is, after the EXEC select statement, I need to check the value
of v1. Thanks
"Omnibuzz" wrote:
> Insert into table variable will not work for EXEC.
> You need to use a temporary table.
> Hope this helps.
> "Jen" wrote:
>|||Please read the KB again, and look and sp_executesql in the Books Online.
It can take a variable.
use pubs
declare @.table sysname, @.sqlstring nvarchar(200)
set @.table = 'authors'
set @.sqlstring = 'select * from ' + @.table
exec sp_executesql @.sqlstring
Please tell us exactly what you are trying to accomplish
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:5C84D4B8-FBC8-4250-B813-B272EA250563@.microsoft.com...
> Thanks, I read all. I read the response to other post, and I used it in my
> first line "
> SELECT TOP 1 @.tablename=tableName FROM @.t1 ". I read the KB too, but
> sp_executesql' statement can't have variable, I have to use @.tablename
> variable.
> And I am using sql2000 sp3.
> "Kalen Delaney" wrote:
>