Creating a cursor using results from CTE 2

[Kflasph]

I have built a Common Table Expression that gives me the results I need and I would like to loop through the results to run other selects with each record.

Is it possible to create a cursor with the results of a CTE?

I can run the CTE then select the information from the table in a simple select statement after the CTE is established:
Select * from tmpCTE

but when I change the code to read:

Declare tmpCursor cursor for
Select * from tmpCTE

I receive an error:
Incorrect syntax near the keyword 'Declare'.

Thanks,
KFlasph

 

[ca8msm]

What specific reason do you have for needing a cursor? Cursors are generally bad for performance and they can often be avoided.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Crowley16]

I don't think you can use a cte for a cursor, why not dump the records into a temp table or table variable and select from there instead?

cursors do tend to be slow, but not necessarily as slow trying to avoid them in certain situations...

--------------------
Procrastinate Now!

 

[Kflasph]

I ended up creating a temp table to hold the values but I was just wondering if it could be done.

Thanks for the quick responses !

KFlasph

 

[ca8msm]

cursors do tend to be slow, but not necessarily as slow trying to avoid them in certain situations...

Yes, there are certain cases where cursors can't be avoided. For cases where they can (and this is by far the majority of cases), a set based solution will be much quicker.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

Crowley I have to disagree. The effort to avoid the cursor only happens once, the cursor affects performance every time it is run. It is almost always worth the time to avoid the cursor. Plus as you get more expereinced with thinking in set-based terms, usually there is no additonal effort at all becasue you know how to avoid the cursor. Cursors are very very rarely necessary.

"NOTHING is more important in a database than integrity." ESquared

 

[Crowley16]

ok, if you need to loop through a set of different databases and perform various stored procedures against each database.

I could setup temp tables and while loops and matched indexes to do that, but frankly, it'd be trying to mimic what a cursor is doing anyway, and not as well, so I'd use a cursor in such cases.

Sometimes it's not possible to use a set based approach and in those cases, cursors are faster than trying to construct your own loops...

--------------------
Procrastinate Now!

 

[gmmastros]

Crowley,

I think the point that people are trying to make is the Loops in SQL Server are MUCH slower than set based operations.

Quite some time ago, there was a SQL Server puzzle to calculate the prime numbers up to 1,000,000. I think the best time achieved was approximately 2 seconds, which really is quite amazing. I re-wrote the same logic in a VB6 app, and it executed in 0.2 seconds. Yup. It was 10 times faster in VB. The reason the T-SQL code was slower is because it involved loops.

It doesn't matter if the loop is based on a cursor or a while structure. Loops are slow in SQL Server. Period. So, if you can find a set based method to accomplish the same goal, it will execute in less time.

There are some exceptions to this, of course. For example, calculating a running total is faster using a cursor/loop method.

The point is, most results can be achieved using set based operations, and only a small fraction will be faster using a loop.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BritDBA]

Hi KFlasph,

Yes you can use a CTE in the select statement of a cursor defintion. Just create the CTE first and verify that it works correctly and give you the results that you want.

Next add the DECLARE CURSOR statement immediately before the WITH statment:

DECLARE cur_TestCURSOR FOR

WITH TabA AS
(
SELECT col1, col2
FROM TableA
WHERE col1 > 'x'
)
SELECT a.col1, a.col2, b.col2
FROM TabA a
JOIN TableB b
ON a.col1 = b.col1

If you are getting an error check the syntax of the SELECT statement. There are some limitations on the SELECT statement used in a CURSOR as defined in books online.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/5a3a27aa-03e8-4c98-a27e-809282379b21.htm

 

[Kflasph]

Thanks for the reply ! I was doing the process the wrong way. I was doing my CTE select first and then trying to build the cursor from the results.
The way you have it makes sense now - I was trying to put the cart before the horse.

Thanks so much ! Star for you !
KFlasph