0

I ran into an error recently while fetching from a cursor on a query that utilized a CTE.

"[Microsoft][SQL Server native Client 10.0]Connection is bust with results for another command"

The error was occurring on a subsequent cursor that is executed on each iteration of the fetch.

I was able to resolve the error by replacing the CTE with a derived table.

I need to know why the derived table works fine, while the CTE fails, and if I did something wrong in the CTE example.

The original query was relatively complex, involving multiple joins, but basically amounts to the following:

WITH cteResults AS (
    SELECT Account.Id AS AccountId
    FROM Accounts AS Account
    WHERE Account.Number = '12345'
    UNION
    SELECT SubAccount.Id
    FROM SubAccounts AS SubAccount
    WHERE SubAccount.Number = '12345')

SELECT
    Invoice.Value AS InvoiceValue,
    CASE
        WHEN Representative.Sequence IS NOT NULL THEN THEN Representative.Name
        ELSE Account.OwnerName
    END AS InvoiceName
FROM cteResults
INNER JOIN Invoices AS Invoice ON
    Invoice.AccountId = cteResults.AccountId
LEFT OUTER JOIN Accounts AS Account ON
    Account.Id = Invoice.AccountId
LEFT OUTER JOIN AccountRepresentatives AS Representative ON
    Representative.Id = Invoices.AccountRepresentativeId

The code in question would loop through a cursor using the above statement, and for each iteration of FETCH, it would execute a second cursor:

FOREACH InvoicesCursor INTO InvoiceResults.*
    OPEN FormattingRulesCursor
    FETCH FormattingRulesCursor into FormattingRules.*
    // Error appears here
    CLOSE FormattingRulesCursor
END FOREACH

Any cursor that is opened from the application during the FOREACH statement fails with the error I mentioned above.

However, if I drop the CTE and use a derived table, I do not receive the error and everything works appropriately.

SELECT
    Invoice.Value AS InvoiceValue,
    CASE
        WHEN Representative.Sequence IS NOT NULL THEN THEN Representative.Name
        ELSE Account.OwnerName
    END AS InvoiceName
FROM (SELECT Account.Id AS AccountId
    FROM Accounts AS Account
    WHERE Account.Number = '12345'
    UNION
    SELECT SubAccount.Id
    FROM SubAccounts AS SubAccount
    WHERE SubAccount.Number = '12345') AS cteResults
INNER JOIN Invoices AS Invoice ON
    Invoice.AccountId = cteResults.AccountId
LEFT OUTER JOIN Accounts AS Account ON
    Account.Id = Invoice.AccountId
LEFT OUTER JOIN AccountRepresentatives AS Representative ON
    Representative.Id = Invoices.AccountRepresentativeId
StrykerJW
  • 21
  • 4
  • Not directly related to your question, but, what operations are being performed by the cursors? Have you considered a set based approach? – Dan Jan 20 '14 at 21:05
  • The query results are consumed by the application to populate a program array for a browse screen. My example has been overly simplified for brevity. In this case the second query actually belongs to another department within my company and is part of a business logic component used to format data elements for display. I'm sure that this logic would have been better implemented as part of a SP, but unfortunately I don't have any control over that. – StrykerJW Jan 20 '14 at 21:22

0 Answers0