473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Declare dynamic Cursor from String

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
5 13823
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
(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
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
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
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Arijit Chatterjee | last post by:
Hi Everybody, I have a probs with dynamic generation. I am writing the probs ====================================== create proc test as declare @query varchar(500) set @query = 'select * from...
1
by: Kevin Frey | last post by:
Hello, I have a test database with table A containing 10,000 rows and a table B containing 100,000 rows. Rows in B are "children" of rows in A - each row in A has 10 related rows in B (ie. B has...
1
by: Frank Swarbrick | last post by:
Given the following COBOL statements with embedded SQL... Select-ppissue. EXEC SQL DECLARE c1 CURSOR FOR SELECT ACCOUNT_NUMBER, SERIAL_NUMBER, AMOUNT,
2
by: Łukasz W. | last post by:
Hello everybody! I have a small table "ABC" like this: id_position | value --------------------------- 1 | 11 2 | 22 3 | 33
0
by: JimSnyder | last post by:
I am trying to learn how to use a basic natural dynamic cursor for use in Pro*C and am getting the following error I cannot get past: "Missing IN or OUT parameter at index:: 1" Here is the query...
1
by: satishchandrat | last post by:
Hi, This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure. I have my SQL string exeeding more than 4000...
2
kiss07
by: kiss07 | last post by:
Hi, What is difference between dynamic cursor and static cursor? what is plsql table? Waiting for reply.. Thanks, Arun..
0
by: ajexpert | last post by:
Ok, here it is I need define a cursor having select statement with dynamic where clause. Please let me know if the query is not clear. I belive what I am asking is not the Rocket Science. ...
0
by: kino | last post by:
Hello, I need to declare a protected string array in my .cs file to make it accessible in the .aspx file of the same page, could anyone please let me know the syntax of declaring a protected...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.