title: Loop through a cursor in SQL Server
body: <p>I would like to understand how I can use the content of a cursor in a Where clause of a SQL query.</p>
  
  <p>I tried to run the code below and I get no result.</p>
  
  <p>The field salescostindex allows me to recognise the specific sales and costs for each transaction with a customer. Now I need to understand when the costs are booked to a cost centre and the corresponding sales are booked to another. This is why I need the looping, I need to exclude a cost centre from the sales account 100001 for each selection of the same cost centre in the cost account 200001.
  Now when I use a cost centre where I know this is happening in the routine, this works, nevertheless when I let it loop across the cursor no result is returned.`</p>
  
  <p>Here is a sample of the data.
  in the SalesCostIndex field you see that number 000010, 000105, 002008 appear in ledger 200001 in one cost centre and in ledger 100001 in another cost centre.
  So I would like to have a query that picks up only the salescostindex that appear in ledger 200001 in one cost centre and in ledger 100001 in a different cost centre.
  Now the cost centres in the real dataset are more than 100.</p>
  
  <pre><code>+------------+------------+----------------+--------+-------------+
  |    Date    | CostCentre | SalesCostIndex | Ledger | SalesAmount |
  +------------+------------+----------------+--------+-------------+
  | 2018-09-21 |        100 |         000010 | 100001 |        -100 |
  | 2018-09-01 |        100 |         000105 | 100001 |        -154 |
  | 2018-08-23 |        100 |         002008 | 200001 |          67 |
  | 2018-08-23 |        100 |         001525 | 200001 |          45 |
  | 2018-07-21 |        101 |         001731 | 100001 |        -101 |
  | 2018-08-23 |        101 |         000010 | 200001 |          80 |
  | 2018-08-23 |        101 |         001964 | 200001 |          75 |
  | 2018-08-23 |        101 |         002001 | 200001 |         112 |
  | 2018-08-23 |        102 |         002245 | 200001 |         210 |
  | 2018-08-23 |        102 |         000105 | 200001 |         125 |
  | 2018-08-23 |        102 |         002008 | 100001 |         -91 |
  | 2018-08-27 |        102 |         002115 | 200001 |          25 |
  +------------+------------+----------------+--------+-------------+
  </code></pre>
  
  <p>I could not try an approach that included inserting into a temporary table. This is because I don't have a write access to the database and I can't create objects, hence I have to stick only to results from <code>SELECT</code> statements or any statement that does not write to the database.</p>
  
  <pre><code>DECLARE @CC VARCHAR(MAX)
  
  DECLARE CCcursor CURSOR
      FOR SELECT DISTINCT CostCentre FROM
      LedgerTransactions
      WHERE   
      Ledger='200001' and
      Date = '2018-08-23' and
      CostCentre like '1__'   
  
  OPEN CCcursor
  FETCH NEXT FROM CCcursor
  INTO @CC
  
  WHILE @@FETCH_STATUS = 0
  
  BEGIN
  
      SELECT CostCentre,
             SalesCostIndex,
             Sum(SalesAmount) as TotalSales
      FROM
      LedgerTransactions
      WHERE        
      Ledger='100001' and
      CostCentre not in (@CC) and
      SalesCostIndex in
          (Select Distinct SalesCostIndex
          FROM LedgerTransactions
          WHERE        
          Ledger='200001' and
          Date = '2018-08-23' AND
          CostCentre in (@CC))
      GROUP BY
      CostCentre, SalesCostIndex
  
  FETCH NEXT FROM CCcursor
  INTO @CC
  
  END
  
  CLOSE CCcursor
  </code></pre>