SQL Server Performance Forum – Threads Archive

CTE to replace cursor

Hi, I am trying to replace a cursor with a CTE. Is it possible to scroll through the records returned by the CTE? i.e. If I get more than one row returned check the values in each row? A bit like a result set? TIA Jamie…

Not yet worked on those on a full scale, but I hope the articles below would help you to understand:
http://www.databasejournal.com/features/mssql/article.php/3502676 http://www.sqlservercentral.com/columnists/sSampath/commontableexpressionsinsqlserver2005.asp Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya, Thanks for those links. Still no joy I’m afraid. Please see Sql below if it helps. If I have: select 1 AS Counter, custid, code from customer where custid IN (8072, 7786) which returns: CUSTID CODE
7786

Code:

8072#
I turn this into a CTE
WITH CustCte(CustID, Code)
AS
    (SELECT CustID, Code FROM dbo.Customer WHERE CustID IN (8072, 7786))
SELECT CustID, Code FROM CustCte
GO
Which naturally gives me:
CUSTID  CODE
7786[code]
8072#
I now want navigate through each row i.e.
For first row if Code = XYZ then do something. else move to next row
If next row Code = XYX then do something.
I came up with the following and various different variations but can't get the Counter to increment.
WITH CustCte(Counter, CustID, Code)
AS
    (SELECT 1 AS Counter, CustID, Code FROM dbo.Customer WHERE CustID IN (8072, 7786))
SELECT Counter = Counter + 1, CustID, Code FROM CustCte
do you have further thoughts.
Jamie

]]>