存储过程如下:if object_id('SP_SellGD') is not null
drop proc SP_SellGD
go
CREATE PROCEDURE SP_SellGD
(
@TableName VARCHAR (255),--表名
@DH VARCHAR (20), --单号
@UpDate VARCHAR (50),--日期
@UserID VARCHAR (10), --登陆用户ID
@ZJE money , --总金额
@Memo VARCHAR (50),--备注
@BZGroup VARCHAR (8)--班组ID
)
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)
DECLARE @SQL1 NVARCHAR(4000)
DECLARE @error_var int, @rowcount_var int,@Error_count int
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @SQL=N'
UPDATE '+@TableName
SET @SQL=@SQL+N' set Odd_numbers='''+@DH+''''
SET @SQL=@SQL+N',Sell_Date='''+@UpDate+''''
EXEC(@SQL)
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF (@error_var <> 0) OR (@rowcount_var = 0)
BEGIN
SELECT @Error_count = 1
END Delete from SellBilld_Table where Odd_numbers=''+@DH+''
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF (@error_var <> 0) OR (@rowcount_var = 0)
BEGIN
SELECT @Error_count = 2
END
Insert into SellBilld_Table(Odd_numbers,Sell_Date,E_ID,R_Total,G_BM,Memo_Str)
Values(''+@DH+'',''+@UpDate+'',''+@UserID+'',+@ZJE,''+@BZGroup+'',''+@Memo+'')
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF (@error_var <> 0) OR (@rowcount_var = 0)
BEGIN
SELECT @Error_count = 3
END SET @SQL1=N'
Insert into SellList '
SET @SQL1=@SQL1+N'
(Odd_numbers,Sell_Date,R_ID,R_Price,R_Number,R_Total,R_Memo)
select Odd_numbers,Sell_Date,R_ID,R_Price,R_Number,R_Total,R_Memo
from '
SET @SQL1=@SQL1+N''+@TableName
EXEC(@SQL1)
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF (@error_var <> 0) OR (@rowcount_var = 0)
BEGIN
SELECT @Error_count = 4
END IF (@Error_count=1) OR (@Error_count=2) OR (@Error_count=3) OR (@Error_count=4)
BEGIN
ROLLBACK TRANSACTION --当有错误发生时整个SP全部回滚
IF @Error_count=1
BEGIN
select -1 as ErrorNo,'更新销售出库临时表数据失败!Error in sp:SP_SellGD' as ErrorMessage
return -1 --返回-1,表示SQL执行失败
END
IF @Error_count=2
BEGIN
select -1 as ErrorNo,'删除销售出库单据表失败!Error in sp:SP_SellGD' as ErrorMessage
return -1 --返回-1,表示SQL执行失败
END
IF @Error_count=3
BEGIN
select -1 as ErrorNo,'插入数据到销售出库单据表失败!Error in sp:SP_SellGD' as ErrorMessage
return -1 --返回-1,表示SQL执行失败
END
IF @Error_count=4
BEGIN
select -1 as ErrorNo,'插入数据到销售出库明细表失败!Error in sp:SP_SellGD' as ErrorMessage
return -1 --返回-1,表示SQL执行失败
END
END
ELSE BEGIN
COMMIT TRANSACTION
select 0
return 0 --返回0,表示SQL执行成功
END
END
GO调用如下:
ASPC := TADOStoredProc.Create(self);
try
with ASPC do
begin
Connection := DM.ACN; //TADOConnection
CLose;
Parameters.Clear;
ProcedureName := 'SP_Sell';
Parameters.Refresh;
Parameters.ParamByName('@TableName').Value := Temp_Table;
Parameters.ParamByName('@DH').Value := Lab_DH.Caption;
Parameters.ParamByName('@UpDate').Value := Sell_D;
Parameters.ParamByName('@UserID').Value := DM.BUser_ID;
Parameters.ParamByName('@ZJE').Value :=
DBSumList1.SumCollection.Items[0].SumValue;
Parameters.ParamByName('@Memo').Value := Memo_EDT.Text;
Parameters.ParamByName('@BZGroup').Value := BZGroup;
Prepared := true;
try
Open;
if Parameters.ParamByName('@RETURN_VALUE').Value = 0 then
begin//成功
PrintreportClick(self);
DM.PopMSG(Lab_DH.Caption); //显示弹出式提示窗体
AppendLog('销售出库单过帐,单号:'+Lab_DH.Caption,'成功');
InitialzeDH;
end
else begin //失败
AppendLog('销售出库单过帐,单号:'+Lab_DH.Caption,'失败');
MSB('由于以下原因过帐失败!'+#10+#13+Fields[1].AsString,2);
end;
except
On E:Exception do
begin
if POS('约束 '+'''CK_RawMaterials_T'''+' 冲突',E.Message) <> 0 then
begin
MSB('某些商品库存不足,无法过帐!',1);
end;
DM.PopMSG('由于以下原因过帐失败!'+#13+#10+E.Message,'1');
end;
end;
end; //end with
finally
ASPC.Close;
ASPC.Free;
end;现在代码是可以通过,不过我不知道会不会存在什么问韪?
drop proc SP_SellGD
go
CREATE PROCEDURE SP_SellGD
(
@TableName VARCHAR (255),--表名
@DH VARCHAR (20), --单号
@UpDate VARCHAR (50),--日期
@UserID VARCHAR (10), --登陆用户ID
@ZJE money , --总金额
@Memo VARCHAR (50),--备注
@BZGroup VARCHAR (8)--班组ID
)
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)
DECLARE @SQL1 NVARCHAR(4000)
DECLARE @error_var int, @rowcount_var int,@Error_count int
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @SQL=N'
UPDATE '+@TableName
SET @SQL=@SQL+N' set Odd_numbers='''+@DH+''''
SET @SQL=@SQL+N',Sell_Date='''+@UpDate+''''
EXEC(@SQL)
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF (@error_var <> 0) OR (@rowcount_var = 0)
BEGIN
SELECT @Error_count = 1
END Delete from SellBilld_Table where Odd_numbers=''+@DH+''
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF (@error_var <> 0) OR (@rowcount_var = 0)
BEGIN
SELECT @Error_count = 2
END
Insert into SellBilld_Table(Odd_numbers,Sell_Date,E_ID,R_Total,G_BM,Memo_Str)
Values(''+@DH+'',''+@UpDate+'',''+@UserID+'',+@ZJE,''+@BZGroup+'',''+@Memo+'')
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF (@error_var <> 0) OR (@rowcount_var = 0)
BEGIN
SELECT @Error_count = 3
END SET @SQL1=N'
Insert into SellList '
SET @SQL1=@SQL1+N'
(Odd_numbers,Sell_Date,R_ID,R_Price,R_Number,R_Total,R_Memo)
select Odd_numbers,Sell_Date,R_ID,R_Price,R_Number,R_Total,R_Memo
from '
SET @SQL1=@SQL1+N''+@TableName
EXEC(@SQL1)
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
IF (@error_var <> 0) OR (@rowcount_var = 0)
BEGIN
SELECT @Error_count = 4
END IF (@Error_count=1) OR (@Error_count=2) OR (@Error_count=3) OR (@Error_count=4)
BEGIN
ROLLBACK TRANSACTION --当有错误发生时整个SP全部回滚
IF @Error_count=1
BEGIN
select -1 as ErrorNo,'更新销售出库临时表数据失败!Error in sp:SP_SellGD' as ErrorMessage
return -1 --返回-1,表示SQL执行失败
END
IF @Error_count=2
BEGIN
select -1 as ErrorNo,'删除销售出库单据表失败!Error in sp:SP_SellGD' as ErrorMessage
return -1 --返回-1,表示SQL执行失败
END
IF @Error_count=3
BEGIN
select -1 as ErrorNo,'插入数据到销售出库单据表失败!Error in sp:SP_SellGD' as ErrorMessage
return -1 --返回-1,表示SQL执行失败
END
IF @Error_count=4
BEGIN
select -1 as ErrorNo,'插入数据到销售出库明细表失败!Error in sp:SP_SellGD' as ErrorMessage
return -1 --返回-1,表示SQL执行失败
END
END
ELSE BEGIN
COMMIT TRANSACTION
select 0
return 0 --返回0,表示SQL执行成功
END
END
GO调用如下:
ASPC := TADOStoredProc.Create(self);
try
with ASPC do
begin
Connection := DM.ACN; //TADOConnection
CLose;
Parameters.Clear;
ProcedureName := 'SP_Sell';
Parameters.Refresh;
Parameters.ParamByName('@TableName').Value := Temp_Table;
Parameters.ParamByName('@DH').Value := Lab_DH.Caption;
Parameters.ParamByName('@UpDate').Value := Sell_D;
Parameters.ParamByName('@UserID').Value := DM.BUser_ID;
Parameters.ParamByName('@ZJE').Value :=
DBSumList1.SumCollection.Items[0].SumValue;
Parameters.ParamByName('@Memo').Value := Memo_EDT.Text;
Parameters.ParamByName('@BZGroup').Value := BZGroup;
Prepared := true;
try
Open;
if Parameters.ParamByName('@RETURN_VALUE').Value = 0 then
begin//成功
PrintreportClick(self);
DM.PopMSG(Lab_DH.Caption); //显示弹出式提示窗体
AppendLog('销售出库单过帐,单号:'+Lab_DH.Caption,'成功');
InitialzeDH;
end
else begin //失败
AppendLog('销售出库单过帐,单号:'+Lab_DH.Caption,'失败');
MSB('由于以下原因过帐失败!'+#10+#13+Fields[1].AsString,2);
end;
except
On E:Exception do
begin
if POS('约束 '+'''CK_RawMaterials_T'''+' 冲突',E.Message) <> 0 then
begin
MSB('某些商品库存不足,无法过帐!',1);
end;
DM.PopMSG('由于以下原因过帐失败!'+#13+#10+E.Message,'1');
end;
end;
end; //end with
finally
ASPC.Close;
ASPC.Free;
end;现在代码是可以通过,不过我不知道会不会存在什么问韪?
你把可能出现问题的地方找出来,然后再让大家具体给你看看。