467,911 Members | 1,381 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,911 developers. It's quick & easy.

Help on creating a user function.

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
Jul 25 '05 #1
  • viewed: 1307
Share:
2 Replies
When I use:
insert @FeatRequestStatus
exec @sqlPlan
It says "execute can be used as a source when insert into a table viarable"
"Kevin" <hu*@lucent.com> wrote in message
news:dc********@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

Jul 25 '05 #2
Kevin (hu*@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 25 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by tr1n1x | last post: by
6 posts views Thread by D | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.