CTE to replace cursor | SQL Server Performance Forums

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
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
I turn this into a CTE
WITH CustCte(CustID, Code)
    (SELECT CustID, Code FROM dbo.Customer WHERE CustID IN (8072, 7786))
SELECT CustID, Code FROM CustCte
Which naturally gives me:
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)
    (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.