DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
@.sqlPlan
But it is not true.Who can correct for me.
Another question is :
How to execute a sql statement state by a variable "@.sqlPlan" and
insert the result to a table "@.FeatRequestStatus"?
I am a new hand of sql programming.Thank you very much for your helpWhen I use:
insert @.FeatRequestStatus
exec @.sqlPlan
It says "execute can be used as a source when insert into a table viarable"
"Kevin" <hua@.lucent.com> wrote in message
news:dc2mgs$16f@.netnews.proxy.lucent.com...
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.
> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?
> I am a new hand of sql programming.Thank you very much for your help|||Kevin (hua@.lucent.com) writes:
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.
You need to say:
EXEC ('DECLARE rs CURSOR GLOBAL FAST_FORWARD ' + @.sqlPlan)
Note that I changed LOCAL to GLOBAL here. This is necessary, since the
cursor is accessed from a different scope than it is created.
> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?
INSERT EXEC does not work with table variables, as you have experienced.
Use a temp table instead.
And if @.sqlPlan is an SQL statement, the syntax is
EXEC(@.sqlPlan)
The syntax you had on your other post:
EXEC @.sqlPlan
means "execute the stored procedure of which the name is in @.sqlPlan".
> I am a new hand of sql programming.Thank you very much for your help
In such case, I should maybe point out, that cursors is something
to be used sparingly. There are situations where cursors can be
motivated, but they often come with a price of severly reduced
performance. Work set-based if you can.
Dynamic SQL is not really anything for beginners - it's definitely an
advanced feature. Dynamic SQL makes things a lot more complex, and
avoid if you can. I have a longer article on dynamic SQL on my web
site that you could find useful:
http://www.sommarskog.se/dynamic_sql.html
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
No comments:
Post a Comment