在错误的列中插入值的存储过程

Question

>我遇到了一个奇怪的问题， 我的 c# 程序正在将值插入数据库(基本上检查记录是否存在然后更新它，否则插入到表中)

存储过程和表的查询如下所示：

桌子

CREATE TABLE [dbo].[PurchaseReceiptbySheetOil](
[id] [int] IDENTITY(1,1) NOT NULL,
[LPOId] [int] NULL,
[POLineNumber] [nvarchar](100) NULL,
[Serial] [nvarchar](100) NULL,
[DateLoaded] [date] NULL,
[truck] [nvarchar](100) NULL,
[Trailer] [nvarchar](100) NULL,
[Transporter] [nvarchar](100) NULL,
[Driver] [nvarchar](max) NULL,
[PassportNumber] [nvarchar](100) NULL,
[ObserverdVolume] [nvarchar](100) NULL,
[Terminal] [nvarchar](100) NULL,
[Tank] [nvarchar](100) NULL,
[TempInTank] [nvarchar](100) NULL,
[VCF20] [float] NULL,
[VolumeLTS20C] [float] NULL,
[Density20C] [float] NULL,
[WeightMTons] [float] NULL,
[Destination] [nvarchar](max) NULL,
[BottomSEALNRS] [nvarchar](max) NULL,
[TopSealNRS] [nvarchar](100) NULL,
[SAMPLESEALNRS] [nvarchar](max) NULL,
[PhysicalDipsOnTheTruckinMM1] [int] NULL,
[PhysicalDipsOnTheTruckinMM2] [int] NULL,
[PhysicalDipsOnTheTruckinMM3] [int] NULL,
[PhysicalDipsOnTheTruckinMM4] [int] NULL,
[PhysicalDipsOnTheTruckinMM5] [int] NULL,
[PhysicalDipsOnTheTruckinMM6] [int] NULL,
[Site] [nvarchar](100) NULL,
[LineNumber] [int] NULL,
[CreatedOn] [datetime] NULL,
[UpdatedOn] [datetime] NULL,
[CreatedBy] [nvarchar](100) NULL,
[UpdatedBy] [nvarchar](100) NULL
)

存储过程

CREATE PROCEDURE [dbo].[CreateUpdatePurchaseReceiptbySheetOil]
@Site nvarchar(100),
@CreatedOn datetime,
@UpdatedOn datetime,
@CreatedBy nvarchar(100),
@UpdatedBy nVarchar(100),
@DateLoaded date,
@truck nvarchar(100),
@Trailer nvarchar(100),
@Transporter nvarchar(100),
@Driver nvarchar(max),
@PassportNumber nvarchar(100),
@ObserverdVolume nvarchar(100),
@Terminal nvarchar(100),
@Tank nvarchar(100),
@TempInTank nvarchar(100),
@VCF20 float,
@VolumeLTS20C float,
@Density20C float,
@WeightMTons float,
@Destination nvarchar(max),
@BottomSEALNRS nvarchar(max),
@SAMPLESEALNRS nvarchar(max),
@PhysicalDipsOnTheTruckinMM1 int,
@PhysicalDipsOnTheTruckinMM2 int,
@PhysicalDipsOnTheTruckinMM3 int,
@PhysicalDipsOnTheTruckinMM4 int,
@PhysicalDipsOnTheTruckinMM5 int,
@PhysicalDipsOnTheTruckinMM6 int,
@Serial Nvarchar(100),
@LineNumber int,
@POLineNumber int,
@TopSealNRS nvarchar(100),
@LPOId nvarchar(100)
AS
BEGIN
if exists(select * from [PurchaseReceiptbySheetOil] where Site= @Site and 
Serial=@Serial and LineNumber = @LineNumber)
BEGIN
UPDATE [dbo].[PurchaseReceiptbySheetOil]
SET [DateLoaded] = @DateLoaded,
UpdatedOn =@UpdatedOn,      
UpdatedBy=@UpdatedBy,
[truck] = @truck
,[Trailer] = @Trailer
,[Transporter] = @Transporter
,[Driver] = @Driver
,[PassportNumber] = @PassportNumber
,[ObserverdVolume] = @ObserverdVolume
,[Terminal] = @Terminal
,[Tank] = @Tank
,[TempInTank] = @TempInTank
,[VCF20] = @VCF20
,[VolumeLTS20C] = @VolumeLTS20C
,[Density20C] = @Density20C
,[WeightMTons] = @WeightMTons
,[Destination] = @Destination
,[BottomSEALNRS] = @BottomSEALNRS
,[SAMPLESEALNRS] = @SAMPLESEALNRS
,[PhysicalDipsOnTheTruckinMM1] = @PhysicalDipsOnTheTruckinMM1
,[PhysicalDipsOnTheTruckinMM2] = @PhysicalDipsOnTheTruckinMM2
,[PhysicalDipsOnTheTruckinMM3] = @PhysicalDipsOnTheTruckinMM3
,[PhysicalDipsOnTheTruckinMM4] = @PhysicalDipsOnTheTruckinMM4
,[PhysicalDipsOnTheTruckinMM5] = @PhysicalDipsOnTheTruckinMM5
,[PhysicalDipsOnTheTruckinMM6] = @PhysicalDipsOnTheTruckinMM6
,[Serial] = @Serial
,TopSealNRS=@TopSealNRS
,LPOId=@LPOId
WHERE  Site= @Site and Serial=@Serial and LineNumber = @LineNumber
END
else 
BEGIN

INSERT INTO [dbo].[PurchaseReceiptbySheetOil]
([LPOId]
,[POLineNumber]
,[Serial]
,[DateLoaded]
,[truck]
,[Trailer]
,[Transporter]
,[Driver]
,[PassportNumber]
,[ObserverdVolume]
,[Terminal]
,[Tank]
,[TempInTank]
,[VCF20]
,[VolumeLTS20C]
,[Density20C]
,[WeightMTons]
,[Destination]
,[BottomSEALNRS]
,[TopSealNRS]
,[SAMPLESEALNRS]
,[PhysicalDipsOnTheTruckinMM1]
,[PhysicalDipsOnTheTruckinMM2]
,[PhysicalDipsOnTheTruckinMM3]
,[PhysicalDipsOnTheTruckinMM4]
,[PhysicalDipsOnTheTruckinMM5]
,[PhysicalDipsOnTheTruckinMM6]
,[Site]
,[LineNumber]
,[CreatedOn]
,[UpdatedOn]
,[CreatedBy]
,[UpdatedBy])
VALUES




(@LPOId,@POLineNumber,
@Serial,@DateLoaded,
@truck,@Trailer,@Transporter,@Driver,
@PassportNumber,@ObserverdVolume,@Terminal,@Tank,@TempInTank,
@VCF20,@VolumeLTS20C,@Density20C,@WeightMTons,@Destination,@BottomSEALNRS,
@TopSealNRS,@SAMPLESEALNRS,
@PhysicalDipsOnTheTruckinMM1,@PhysicalDipsOnTheTruckinMM2,
@PhysicalDipsOnTheTruckinMM3,@PhysicalDipsOnTheTruckinMM4,
@PhysicalDipsOnTheTruckinMM5,@PhysicalDipsOnTheTruckinMM6,@Site,
@LineNumber,@CreatedOn,@UpdatedOn,@CreatedBy,@UpdatedBy)

END
END

现在，当我运行以下查询来执行存储过程时：

exec sp_executesql N'EXECUTE [dbo].[CreateUpdatePurchaseReceiptbySheetOil]   
@Site  ,@CreatedOn  ,
@UpdatedOn  ,@CreatedBy
,@UpdatedBy  ,@DateLoaded  ,@truck  
,@Trailer  ,@Transporter  ,@Driver
,@PassportNumber  ,@ObserverdVolume  
,@Terminal  ,@Tank  ,@TempInTank  ,@VCF20
,@VolumeLTS20C  ,@Density20C  ,@WeightMTons  
,@Destination  ,@BottomSEALNRS,@TopSealNRS 
,@SAMPLESEALNRS  ,@PhysicalDipsOnTheTruckinMM1  
,@PhysicalDipsOnTheTruckinMM2  ,@PhysicalDipsOnTheTruckinMM3 
,@PhysicalDipsOnTheTruckinMM4  ,@PhysicalDipsOnTheTruckinMM5  
,@PhysicalDipsOnTheTruckinMM6   ,@Serial  ,@LineNumber
,@POLineNumber,@LPOId',N'@CreatedBy nvarchar(7),@CreatedOn datetime,
@UpdatedBY nvarchar(7),@UpdatedOn datetime,@Site nvarchar(7),
@DateLoaded nvarchar(9),@truck nvarchar(7),@Trailer nvarchar(7),
@Transporter nvarchar(5),@Driver nvarchar(8),@PassportNumber nvarchar(8),
@ObserverdVolume nvarchar(6),@Terminal nvarchar(5),@Tank 
nvarchar(3),@TempInTank nvarchar(4),
@VCF20 nvarchar(6),@VolumeLTS20C nvarchar(6),@Density20C 
nvarchar(6),@WeightMTons nvarchar(6),
@Destination nvarchar(5),@BottomSEALNRS nvarchar(14),@TopSealNRS 
nvarchar(33),@SAMPLESEALNRS nvarchar(7),
@PhysicalDipsOnTheTruckinMM1 nvarchar(4),@PhysicalDipsOnTheTruckinMM2 
nvarchar(4),@PhysicalDipsOnTheTruckinMM3 nvarchar(4),
@PhysicalDipsOnTheTruckinMM4 nvarchar(4),@PhysicalDipsOnTheTruckinMM5 
nvarchar(4),@PhysicalDipsOnTheTruckinMM6 nvarchar(4),
@Serial nvarchar(5),@LineNumber int,@POLineNumber int,@LPOId int',
@CreatedBy=N'Tanveer',
@CreatedOn='2017-05-08 11:50:24.283',
@UpdatedBY=N'Tanveer',
@UpdatedOn='2017-05-08 11:50:24.283',
@Site=N'Site001',
@DateLoaded=N'20-Apr-17',
@truck='',
@Trailer='',
@Transporter=N'00002',
@Driver='',
@PassportNumber=''
@ObserverdVolume=N'40.000',
@Terminal=N'TOTAL',
@Tank=N'223',@TempInTank=N'30.0',@VCF20=N'0.9915',@VolumeLTS20C=N'39.660',
@Density20C=N'0.8219',
@WeightMTons=N'32.597',@Destination=N' DRC ',@BottomSEALNRS=N' 02697558/559 
',@TopSealNRS=N' 02697560 TO 562+02697564 TO 566 ',
@SAMPLESEALNRS=N'781845 ' 
,@PhysicalDipsOnTheTruckinMM1=N'1472',@PhysicalDipsOnTheTruckinMM2=N'1592',
@PhysicalDipsOnTheTruckinMM3=N'1706',@PhysicalDipsOnTheTruckinMM4=N'1832',
@PhysicalDipsOnTheTruckinMM5=N'1808',
@PhysicalDipsOnTheTruckinMM6=N'1362',@Serial=N'00001',@LineNumber=8,
@POLineNumber=0,@LPOId=7

列的值在插入过程中被交换，请注意，串行的值是 0001，但它将插入为 1362 而不是 0001，对于其他一些列，值也会更改(不是全部)。

1. 请注意，我已经检查了插入和 值部分。我正在指定列，但仍然面临此问题。

2. 我删除并再次创建了表。

3. 在 sql 中调试期间 服务器，调试开始时该值是正确的，即 0001 但随后 变化，我不知道为什么。

Answer 1

当我们执行存储过程定义时，它中的参数序列应该是相同的，我假设它不依赖于序列，而是取决于参数名称，我错了。