SQL Cursor Problem CODE 16915 Cursor name already exists

Question

I have a problem with a cursor inside SQL Server stored procedure.

Here is the code:

        if (@pEvento = 84)
            begin
                set @pAsun_mail = @pAsun_mail + ' FUP ' + CONVERT(varchar(10), @pFupID) + ' ' + @pVersion;

                DECLARE @ConsecutivoTemp int, @FechaTemp date, @ValorTemp money, @CondicionTemp varchar(50), @GeneraBoletoTemp bit;
                DECLARE CondicionPago_Cursor CURSOR FOR
                SELECT fccp_Consecutivo, Fecha, Valor, Condicion, fccp_BoletosBancarios FROM [dbo].[fup_CuotasCondicionesPago]
                WHERE fccp_entrada_cot_id = (SELECT [eect_id]
                                                FROM  fup_enc_entrada_cotizacion 
                                                WHERE [eect_fup_id] = @pFupID 
                                                AND [eect_vercot_id] = @pVersion) AND fccp_TipoPago_id = 3;

                OPEN CondicionPago_Cursor;
                FETCH NEXT FROM CondicionPago_Cursor INTO
                    @ConsecutivoTemp, @FechaTemp, @ValorTemp, @CondicionTemp, @GeneraBoletoTemp;

                DECLARE @HTMLTableLeasing varchar(max);
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    SET @HTMLTableLeasing = @HTMLTableLeasing + '<tr>';
                    SET @HTMLTableLeasing = @HTMLTableLeasing + '<td>'+ CONVERT(varchar(3), @ConsecutivoTemp) +'</td>';
                    SET @HTMLTableLeasing = @HTMLTableLeasing + '<td>'+ CONVERT(varchar(20), @FechaTemp) +'</td>';
                    SET @HTMLTableLeasing = @HTMLTableLeasing + '<td>'+ CONVERT(varchar(15), @ValorTemp) +'</td>';
                    SET @HTMLTableLeasing = @HTMLTableLeasing + '<td>'+ @CondicionTemp +'</td>';
                    IF @GeneraBoletoTemp = 1 
                        BEGIN
                            SET @HTMLTableLeasing = @HTMLTableLeasing + '<td><input type="checkbox" disabled checked /></td>';
                        END
                    ELSE
                        BEGIN
                            SET @HTMLTableLeasing = @HTMLTableLeasing + '<td><input type="checkbox" disabled/></td>';
                        END
                    SET @HTMLTableLeasing = @HTMLTableLeasing + '</tr>';

                    FETCH NEXT FROM CondicionPago_Cursor INTO
                    @ConsecutivoTemp, @FechaTemp, @ValorTemp, @CondicionTemp, @GeneraBoletoTemp;
                END

                CLOSE CondicionPago_Cursor
                DEALLOCATE CondicionPago_Cursor

                set @pMsg = @pMsg + @HTMLTableLeasing

            end

And the error says:

Already exists a cursor with name 'CondicionPago_Cursor'

I tried checking again the order or STATEMENTS OPEN, FETCH, CLOSE, DEALLOCATE but I think they have a correct order.

I'm tryin to iterate over a set or records to create dynamically a body of a table, you can see the tags <th> and <td>.

Answer 1

First: the error comes from using the global cursor. So, every time you run this it will run into itself. Add a LOCAL keyword.

DECLARE CondicionPago_Cursor CURSOR LOCAL FOR

But, you want to avoid cursor loops whenever possible. They are much less efficient. You might do something like instead.

SELECT @HTMLTableLeasing = @HTMLTableLeasing + '<tr><td>' + CAST(fccp_Consecutivo 
        AS varchar(3)) + '</td><td>' + 
        CAST(Fecha AS varchar(20)) + '</td><td>' +  
        CAST(Valor AS varchar(15)) + '</td><td>' + 
        Condicion + '</td><td>' + 
        CASE WHEN GeneraBoleto = 1 
            THEN '<input type="checkbox" disabled checked />' 
            ELSE '<input type="checkbox" disabled/>' END +
        '</td></tr>'
    FROM [dbo].[fup_CuotasCondicionesPago]
    WHERE fccp_entrada_cot_id = (SELECT [eect_id]
    FROM  fup_enc_entrada_cotizacion 
    WHERE [eect_fup_id] = @pFupID 
    AND [eect_vercot_id] = @pVersion) AND fccp_TipoPago_id = 3

Answer 2

As @Lamu already mentioned, there are better ways to compose (X)HTML.

Also, there is no need to concatenate strings like in the @JohnInk answer.

Here is a conceptual example for you, including CSS for styling.

It will work starting from SQL Server 2005 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
   ID INT IDENTITY PRIMARY KEY
   , state CHAR(2)
   , city VARCHAR(30)
);
INSERT INTO @tbl (state, city)
VALUES
('FL', 'Miami')
, ('CA', 'Los Angeles')
, ('TX', 'Austin');
-- DDL and sample data population, end

DECLARE @xhtmlBody XML
   , @body NVARCHAR(MAX)
   , @tableCaption VARCHAR(30) = 'US states and cities';

SET @xhtmlBody = (SELECT (
SELECT * FROM @tbl FOR XML PATH('row'), TYPE, ROOT('root'))
.query('<html><head>
            <meta charset="utf-8"/>
            (: including embedded CSS styling :)
            <style>
            table <![CDATA[ {border-collapse: collapse;  width: 300px;} ]]>
            th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
            th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
            tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
            td:nth-child(3) <![CDATA[ {background-color: red;} ]]>
            #green <![CDATA[ {background-color: lightgreen;} ]]>
         </style>
         </head>
         <body>
<table border="1">
   <caption><h2>{sql:variable("@tableCaption")}</h2></caption>
   <thead>
      <tr>
        <th>No.</th>
        <th>State</th>
        <th>City</th>
      </tr>
   </thead>
   <tbody>
{
    for $row in /root/row
    return <tr>
            <td>{data($row/ID)}</td>
            <td>{data($row/state)}</td>
            <td>
            {if ($row/city/text()="Los Angeles") then attribute id {"green"} else ()}
            {data($row/city)}
         </td>
        </tr>
}
</tbody></table></body></html>'));

SELECT @xhtmlBody;

Output

Saved as a file, and tested in any Internet browser