英文:
SQL Cursor Problem CODE 16915 Cursor name already exists
问题
我在SQL Server存储过程中遇到了一个光标的问题。
以下是代码部分:
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
错误信息如下：
> 已经存在一个名为 'CondicionPago_Cursor' 的光标
我尝试再次检查了OPEN、FETCH、CLOSE、DEALLOCATE的顺序，但我认为它们的顺序是正确的。
我试图迭代一组记录，动态创建表格的内容，您可以看到<th>和<td>标记。
英文:
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>.
答案1
得分: 2
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
但是，尽量避免使用游标循环。它们效率要低得多。您可以尝试像这样做：
SELECT @HTMLTableLeasing = @HTMLTableLeasing + '
AS varchar(3)) + '
CAST(Fecha AS varchar(20)) + '
CAST(Valor AS varchar(15)) + '
Condicion + '
CASE WHEN GeneraBoleto = 1
THEN ''
ELSE '' END +
'
'
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
英文:
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
答案2
得分: 2
如@Lamu已经提到的，有更好的方法来组合(X)HTML。
此外，没有必要像@JohnInk的答案中那样连接字符串。
以下是一个概念性示例，包括用于样式化的CSS。
它将从SQL Server 2005开始运行。
SQL
-- DDL和示例数据填充，开始
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和示例数据填充，结束
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"/>
(: 包括嵌入的CSS样式 :)
<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>'));
输出
保存为文件，并在任何互联网浏览器中进行测试
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论