从 C#/ASP.NET 调用存储过程时出错
本文关键字:存储过程 出错 调用 NET ASP | 更新日期: 2023-09-27 18:32:51
以下存储过程pr_generate_sales_order数据库 DB1 的 2 个表中生成记录 SO Header & SO Detail。然后,它将 SO 标头和详细信息记录从 DB1 导出到 DB2。
每当我尝试从 C# 调用此 SP ASP.Net/时,都会生成以下错误消息:名称为"c_customers"的游标不存在,并且不会在数据库中创建任何记录。
但是,如果我在 SSMS 中pr_generate_sales_order直接执行此存储过程,那么它可以正常工作,并在 DB1 和 DB2 中生成记录。
问题出在哪里? 它是否与开始尝试捕获有关... ???
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ------------------------------------------------------------------------------------
-- Name: pr_generate_sales_order
-- ------------------------------------------------------------------------------------
-- Parameters:
-- - N/A
--
-- Description:
-- Generate Sales Order in DB1 & DB2
-- ------------------------------------------------------------------------------------
alter procedure [dbo].[pr_generate_sales_order] () as
Begin
-- --------------------------------------------------------------------------------
-- Variable declaration
-- --------------------------------------------------------------------------------
Declare @v_error_number nvarchar(10)
Declare @v_error_message_en nvarchar(1000)
Declare @v_customer_id int
Declare @v_time_log_id int
Declare @v_hourly_rate numeric(18, 5)
Declare @v_total_time numeric(18, 5)
Declare @v_description nvarchar(max)
Declare @tmp_time_log Table
(
time_log_id int,
customer_id int,
sku_id int,
hourly_rate numeric(18, 5),
hours int,
minutes int
)
-- --------------------------------------------------------------------------------
-- Main program
-- --------------------------------------------------------------------------------
Set @v_error_number = '0'
Set @v_error_message_en = ''
Begin Try
Begin Transaction T1
-- ------------------------------------------------------------------------
--
-- ------------------------------------------------------------------------
Insert Into @tmp_time_log
Select tl.time_log_id as time_log_id
, tl.customer_id as customer_id
, tl.sku_id as sku_id -- 24/09/2013
, tl.hourly_rate as hourly_rate
, tl.hours_rounded as hours_rounded
, tl.minutes_rounded as minutes_rounded
From time_log tl
Where tl.transaction_date between '2013-01-06' and getdate()
-- ------------------------------------------------------------------------
-- Open Cursor
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- Declare Customer Cursor: Get the list of Customer
-- ------------------------------------------------------------------------
Declare c_customers cursor for
Select distinct IsNull(customer_id, 0)
From @tmp_time_log
Open c_customers
Fetch Next From c_customers Into @v_customer_id
While @@fetch_status = 0
Begin
-- ----------------------------------------------------------------
-- Create Sales Order Header for each Customer
-- ----------------------------------------------------------------
Insert Into sales_order_header ...
-- Get Sales Order Header Id
Set @v_sales_order_header_id = scope_identity()
-- ----------------------------------------------------------------
-- Declare Time Log Cursor: Get the list of Time Log by customer in order to Create
-- ----------------------------------------------------------------
Declare c_time_log cursor for
Select time_log_id as time_log_id
, hourly_rate as hourly_rate
, cast( (cast(tl.hours as numeric(18, 5)) + (cast(tl.minutes as numeric(18, 5)) / 60) ) as numeric(18, 5))
as total_time
, sku_id as sku_id
From @tmp_time_log tl
Where customer_id = @v_customer_id
-- ----------------------------------------------------------------
-- Open Cursor
-- ----------------------------------------------------------------
Open c_time_log
Fetch Next From c_time_log
Into @v_time_log_id, @v_hourly_rate, @v_total_time, @v_sku_id
While @@fetch_status = 0
Begin
-- Create Sales Order Detail for Each SO Header
Insert Into sales_order_detail ....
-- Get the next Cursor
Fetch Next From c_time_log Into @v_time_log_id, @v_hourly_rate, @v_total_time, @v_sku_id
End
-- Close & Deallocate Cursor
Close c_time_log
Deallocate c_time_log
-- Get the next Cursor
Fetch Next From c_customers Into @v_customer_id
End
-- Close & Deallocate Cursor
Close c_customers
Deallocate c_customers
-- ------------------------------------------------------------------------
-- Export Sales Order Header & Detail From DB1 to DB2
-- ------------------------------------------------------------------------
Exec [DB2].[dbo].[pr_import_sales_order_from_db1]
--
Commit Transaction T1
End Try
Begin Catch
-- If Error Found Then Rollback Transaction
If @@TRANCOUNT > 0
Begin
-- Close & Deallocate Cursor
Close c_customers
Deallocate c_customers
-- Rollback
Rollback Transaction T1
End
-- Get Error # & Error Message
Select @v_error_number = ERROR_NUMBER()
, @v_error_message_en = 'An error occurred in ' + ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE()
GOTO Step_END
End Catch
-- ------------------------------------------------------------------------------------------
-- Return: Error # & Error message
-- ------------------------------------------------------------------------------------------
STEP_END:
Select @v_error_number as error_number
, @v_error_message_en as error_message_en
End
GO
如果您在
exec [DB2].[dbo].[pr_import_sales_order_from_db1]** //are those asterisks there in your code
然后你已经关闭和解除了c_customers,但是你要在你的catch块中第二次调用那些关闭和解除分配的调用,我认为这就是问题所在。
在最后一条评论后编辑
保罗
您应该按如下方式重新组织 Try 块的最后 4 行。
源语言
Close c_customers
Deallocate c_customers
Exec [DB2].[dbo].[pr_import_sales_order_from_db1]
Commit Transaction T1
改 性
Exec [DB2].[dbo].[pr_import_sales_order_from_db1]
Close c_customers
Deallocate c_customers
Commit Transaction T1
这样做的原因是,如果你CLOSE
和DEALLOCATE
游标,然后在 DB2 SPROC 中发生错误,你将尝试在 catch 块中第二次CLOSE
和DEALLOCATE
游标。
在修改/重新排列的版本中,如果一切正常,您仍将在COMMIT TRAN
之前关闭并释放光标,但如果该 DB2 SPROC 呕吐,它将直接跳转到 catch 块,并且 catch 块中的 CLOSE
& DEALLOCATE
将按预期工作。