Declare dynamic Cursor from String

[peppi911@hotmail.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.sta tus
= 'aktiv'

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

[Jack Vamvas]

Re: Declare dynamic Cursor from String

Not within the stored procedure, but I do know their are some undocumented
sps - such as "sp_cursoro pen" 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

<peppi911@hotma il.com> wrote in message
news:1146129244 .595060.254470@ v46g2000cwv.goo glegroups.com.. .[color=blue]
> 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.sta tus
> = 'aktiv'
>
> Havn't found anything in the net...
> Thanks,
> Peppi
>[/color]

[Erland Sommarskog]

Re: Declare dynamic Cursor from String

(peppi911@hotma il.com) writes:[color=blue]
> 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.sta tus
>= 'aktiv'[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[peppi911@hotmail.com]

Re: Declare dynamic Cursor from String

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

[David Portas]

Re: Declare dynamic Cursor from String

peppi911@hotmai l.com wrote:[color=blue]
> 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.[/color]

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/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: Declare dynamic Cursor from String

(peppi911@hotma il.com) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx