SQL Server Performance

CTE to replace cursor

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by jamie.downs, Jan 17, 2006.

  1. jamie.downs Member

    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...
  2. satya Moderator

  3. jamie.downs Member

    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 

Share This Page