SQLserver 存储过程返回了异常值
写了一个写主表和子表的存储过程，该存储过程在数据库执行时一切正常，写成功返回1，失败返回2。但是在winform界面调用时，不管写正确还是错误都返回数字3，就很奇怪，求解决。
winform界面的代码
public int StockInConfrimNoManifest(string sino, string username, string barcode, Entry_StockInDetail stockInDetail)
{
int ret = 0;
string sqlStr = "STOCK_IN_OP_NOMANIFEST";
string time = stockInDetail.ProductTime.ToString();
Dictionary<int, SqlParaStruct> paraList = new Dictionary<int, SqlParaStruct>();
paraList.Add(0, new SqlParaStruct("@SINo", sino, SqlParaType.ParaDataType_String, ParameterDirection.Input));
paraList.Add(1, new SqlParaStruct("@UserName", username, SqlParaType.ParaDataType_String, ParameterDirection.Input));
paraList.Add(2, new SqlParaStruct("@Model", stockInDetail.Model, SqlParaType.ParaDataType_String, ParameterDirection.Input));
paraList.Add(3, new SqlParaStruct("@InQuantity", stockInDetail.InQuantity, SqlParaType.ParaDataType_Int, ParameterDirection.Input));
paraList.Add(4, new SqlParaStruct("@ProductTime", time, SqlParaType.ParaDataType_String, ParameterDirection.Input));
paraList.Add(5, new SqlParaStruct("@BarCode", barcode, SqlParaType.ParaDataType_String, ParameterDirection.Input));
try
{
using (IDataAccess ida = DataAccessFactory.getInstence())
{
ida.EnterQuery();
ret = ida.ExecuteProcedure(sqlStr, paraList);//1成功 2失败
ida.LeaveQuery();
}
}
catch (Exception ex)
{
Logger.Error(string.Format("[数据库]关闭数据库连接异常:{0}{1}",
ex.Message.ToString(), ex.StackTrace.ToString()));
}
return ret;
}
//SQLserver代码
USE [SMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[STOCK_IN_OP_NOMANIFEST]
@SINo NVARCHAR(50),
@UserName NVARCHAR(50),
@Model NVARCHAR(50),
@InQuantity Int,
@ProductTime NVARCHAR(50),
@BarCode NVARCHAR(50)
AS
BEGIN
DECLARE @RET INT
SET @RET = 1;-- 0成功 2失败
DECLARE @SIID INT
SET @SIID = 0;
DECLARE @Listcount INT
SET @Listcount = 1;
DECLARE @GID INT;
DECLARE @CNo INT;
DECLARE @GridNo NVARCHAR(50);
BEGIN TRANSACTION;--开启事务
SELECT @SIID=SIID FROM [SMS].[dbo].[SMS_StockIn] WHERE SINo = @SINo;
--if写主表
IF @SIID = 0
BEGIN
INSERT INTO [SMS].[dbo].[SMS_StockIn] (
[SINo]
,[UserName]
,[BeginTime]
,[EndTime]
,[UpdateTime]
,[ListCount]
,[DisponseCount])VALUES(@SINo, @UserName, GETDATE(), GETDATETIME(), GETDATE(), @Listcount, @Listcount)SELECT @@IDENTITY;
SET @Listcount = @Listcount+1;
IF @@ERROR <>0
begin
SET @RET = 2
end;
INSERT INTO [SMS].[dbo].[SMS_StockInDetail]
([ListNo]
,[SIId]
,[Model]
,[InQuantity]
,[ProductTime]
,[whetherIn]
,[InTime]
,[UpdateTime])VALUES(
1,
@@IDENTITY,
@Model,
@InQuantity,
@ProductTime,
1,
GETDATE(),
GETDATE());
IF @@ERROR <>0
begin
SET @RET = 2;
end;
--写库存
SELECT @GID = [GID], @CNo = [CNo], @GridNo = [GridNo] FROM [SMS].[dbo].[SMS_CabinetGrid] WHERE [GBarCode] = @BarCode;
IF (@GID is null)
begin
SET @RET = 2;
end;
INSERT INTO [SMS].[dbo].[SMS_Storage] (
[GID]
,[CNo]
,[GridNo]
,[GBarCode]
,[ManufacturerNumber]
,[SupplierNumber]
,[MaterialName]
,[Model]
,[StorageQuantity]
,[ProductTime]
,[BatchNo]
,[UpdateTime])VALUES(
@GID,
@CNo,
@GridNo,
@BarCode,
null,
null,
null,
@Model,
@InQuantity,
@ProductTime,
null,
GETDATE())
IF @@ERROR <>0
begin
SET @RET = 2;
end;
END
ELSE
BEGIN
--else写子表
SELECT @Listcount = COUNT(*) FROM [dbo].[SMS_StockInDetail] WHERE SIID = @SIID;
IF (@Listcount is null)
begin
SET @RET = 2;
end;
INSERT INTO [SMS].[dbo].[SMS_StockInDetail]
([ListNo]
,[SIId]
,[Model]
,[InQuantity]
,[ProductTime]
,[whetherIn]
,[InTime]
,[UpdateTime])VALUES(
@Listcount+1,
@SIID,
@Model,
@InQuantity,
@ProductTime,
1,
GETDATE(),
GETDATE());
--更新主表
IF @@ERROR = 0
begin
UPDATE [SMS].[dbo].[SMS_StockIn] SET ListCount = @Listcount+1 WHERE SIID = @SIID;
if @@ERROR <>0
begin
SET @RET = 2;
end;
End;
SELECT @GID = [GID], @CNo = [CNo], @GridNo = [GridNo] FROM [SMS].[dbo].[SMS_CabinetGrid] WHERE [GBarCode] = @BarCode;
IF (@GID is null)
begin
SET @RET = 2;
end;
INSERT INTO [SMS].[dbo].[SMS_Storage] (
[GID]
,[CNo]
,[GridNo]
,[GBarCode]
,[ManufacturerNumber]
,[SupplierNumber]
,[MaterialName]
,[Model]
,[StorageQuantity]
,[ProductTime]
,[BatchNo]
,[UpdateTime])VALUES(
@GID,
@CNo,
@GridNo,
@BarCode,
null,
null,
null,
@Model,
@InQuantity,
@ProductTime,
null,
GETDATE())
IF @@ERROR <>0
begin
SET @RET = 2;
end;
END
IF @RET = 2
BEGIN
ROLLBACK
END;
ELSE
COMMIT;
print @RET;
RETURN @RET;
END
