By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Declare dynamic Cursor from String

P: n/a
Hi,
is it possible to create a cursor from a dynamic string?
Like:
DECLARE @cursor nvarchar(1000)
SET @cursor = N'SELECT product.product_id
FROM product WHERE fund_amt > 0'

DECLARE ic_uv_cursor CURSOR FOR @cursor

instead of using this

--SELECT product.product_id
--FROM product WHERE fund_amt > 0 -- AND mpc_product.status
= 'aktiv'

Havn't found anything in the net...
Thanks,
Peppi

Apr 27 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Not within the stored procedure, but I do know their are some undocumented
sps - such as "sp_cursoropen" and a few others with "sp_cursor*" which might
be abloe to do the job for you.

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<pe******@hotmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
Hi,
is it possible to create a cursor from a dynamic string?
Like:
DECLARE @cursor nvarchar(1000)
SET @cursor = N'SELECT product.product_id
FROM product WHERE fund_amt > 0'

DECLARE ic_uv_cursor CURSOR FOR @cursor

instead of using this

--SELECT product.product_id
--FROM product WHERE fund_amt > 0 -- AND mpc_product.status
= 'aktiv'

Havn't found anything in the net...
Thanks,
Peppi

Apr 27 '06 #2

P: n/a
(pe******@hotmail.com) writes:
is it possible to create a cursor from a dynamic string?
Like:
DECLARE @cursor nvarchar(1000)
SET @cursor = N'SELECT product.product_id
FROM product WHERE fund_amt > 0'

DECLARE ic_uv_cursor CURSOR FOR @cursor

instead of using this

--SELECT product.product_id
--FROM product WHERE fund_amt > 0 -- AND mpc_product.status
= 'aktiv'


Yes, this is possible, but the question remains: why?

See here for details: http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 27 '06 #3

P: n/a
Thanks for your answers.
I'll have a look at the link.
The WHY ist that once a day the cursor should affect all products and
during the day every 5 minutes reclculate for inaktive ones.
Thats the reason.

Thanks,
mike

Apr 27 '06 #4

P: n/a
pe******@hotmail.com wrote:
The WHY ist that once a day the cursor should affect all products and
during the day every 5 minutes reclculate for inaktive ones.
Thats the reason.


That doesn't explain why you are using a cursor. It also doesn't
explain the need for dynamic SQL. Both are things you should avoid when
you can, I think that was what Erland was trying to get at.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Apr 27 '06 #5

P: n/a
(pe******@hotmail.com) writes:
Thanks for your answers.
I'll have a look at the link.
The WHY ist that once a day the cursor should affect all products and
during the day every 5 minutes reclculate for inaktive ones.
Thats the reason.


That does not explain the cursor - but could be that there is some
calculations are too complex to be carried out set-based. But there is
all reason to avoid the iteration if possible and handle all rows at
once. If there are many products this could mean serious reduction in
execution time.

On the other hand, there is enough information for me to tell that you
don't need any dynamic SQL. There are two possible solutions:

DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND (@runforall = 1 OR fund_amt > 0)

If there is an index on the selection column for active products, it's
better to do:

IF @runforall = 1
BEGIN
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
END
ELSE
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND fund_amt > 0
END

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 27 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.