存储过程在表的自动增量列中存储 0

本文关键字：存储 存储过程 | 更新日期: 2023-09-27 18:37:15

>我有下表，其中ProspectCode是Identity Not Null

Table LeadMastersNew
ProspectCode    int 
CompanyName nvarchar(50)    
PersonName  nvarchar(50)    
Designation nvarchar(50)
Number  nvarchar(50)    
Number2 nvarchar(50)    
Emailaddress    nvarchar(50)    
Address nvarchar(MAX)   
Address2    nvarchar(MAX)
CityName    nvarchar(50)    
State   nvarchar(50)    
PinNumber   nvarchar(50)    
Product nvarchar(50)    
RemarkNote  nvarchar(MAX)

我最近面临的问题是，当我使用存储过程将记录存储到上面的表中时，ProspectCode 对于我添加的所有行始终设置为 0。我在上表中有 160 条记录，但是当我添加新记录时，我添加的所有记录的 ProspectCode 都设置为 0。

存储过程

ALTER Procedure [dbo].[Proc_InsertLeads]
 @ProspectCode nvarchar(50),@CompanyName nvarchar(50),@PersonName nvarchar(50),@Designation nvarchar(50),@Number nvarchar(50),  
 @Number2 nvarchar(50),@Emailaddress nvarchar(50),@Address nvarchar(MAX),@Address2 nvarchar(MAX),   
 @CityName nvarchar(50),@State nvarchar(50),@PinNumber nvarchar(50),@Product nvarchar(50),@RemarkNote nvarchar(MAX)
 AS
 BEGIN
      SET IDENTITY_INSERT LeadMastersNew ON;
      INSERT INTO LeadMastersNew
      (ProspectCode,CompanyName,PersonName,Designation,Number,Number2,Emailaddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote) 
      VALUES(@ProspectCode,@CompanyName,@PersonName,@Designation,@Number,@Number2,@Emailaddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote) 
      INSERT INTO LoggerLeadMasters
      (ProspectCode,CompanyName,PersonName,Designation,Number,Number2,Emailaddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote,Activity,ActivityTime) 
      VALUES(@ProspectCode,@CompanyName,@PersonName,@Designation,@Number,@Number2,@Emailaddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote,'New Record Added',getdate()) 
      SET IDENTITY_INSERT LeadMastersNew OFF;
END  

EXEC Proc_InsertLeads'ABc','Mr abc','MD','PhoneNumber','','abc@abcindia.com','xyz','','Mumbai','Maharashtra','400059','Abc', 'Abc'
Sets ProspectCode to 0

谁能帮我解决这个问题？是否必须更改存储过程或表架构？

谢谢

存储过程在表的自动增量列中存储 0

如果您希望标识列自动分配数字，那么您真正不应该做的事情是将IDENTITY_INSERT设置为ON。启用该设置时，SQL Server 会说"相信我，我将在标识列中提供值"。

您可能希望编写如下代码：

BEGIN
     DECLARE @NewID int
      INSERT INTO LeadMastersNew
      (/*ProspectCode,*/CompanyName,PersonName,Designation,Number,Number2,Emailaddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote) 
      VALUES(/*@ProspectCode,*/@CompanyName,@PersonName,@Designation,@Number,@Number2,@Emailaddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote) 
     SET @NewID = SCOPE_IDENTITY()
      INSERT INTO LoggerLeadMasters
      (ProspectCode,CompanyName,PersonName,Designation,Number,Number2,Emailaddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote,Activity,ActivityTime) 
      VALUES(@NewID ,@CompanyName,@PersonName,@Designation,@Number,@Number2,@Emailaddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote,'New Record Added',getdate()) 
END

这可能不会在第一次完全有效，因为我无法协调您显示的代码与您调用它的方式。如果ProspectCode确实是int列，并且您实际上正在尝试插入'Choice Brokers'的nvarchar值，则应该会收到错误。

如果设置自动增量，则不需要identity_insert

删除行

设置IDENTITY_INSERT领导大师新开;

设置IDENTITY_INSERT 领导大师新关闭;

它应该工作

这里ProspectCode必须设置为Auto-increment，因为它是表的标识列。所以基本上在存储过程中，您必须删除输入参数@ProspectCode并且在插入新行时不要在表上打开关闭IDENTITY_INSERT，最终存储过程如下所示：

ALTER Procedure [dbo].[Proc_InsertLeads]
@CompanyName nvarchar(50),@PersonName nvarchar(50),@Designation nvarchar(50),@Number nvarchar(50), 
@Number2 nvarchar(50),@Emailaddress nvarchar(50),@Address nvarchar(MAX),@Address2 nvarchar(MAX),
@CityName nvarchar(50),@State nvarchar(50),@PinNumber nvarchar(50),@Product nvarchar(50),@RemarkNote nvarchar(MAX)
 AS
 BEGIN
  DECALRE @ID INT = 0
  INSERT INTO LeadMastersNew
  (CompanyName,PersonName,Designation,Number,Number2,Emailaddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote) 
  VALUES(@CompanyName,@PersonName,@Designation,@Number,@Number2,@Emailaddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote) 
  SET @ID = SCOPE_IDENTITY() 
  INSERT INTO LoggerLeadMasters
  (ProspectCode,CompanyName,PersonName,Designation,Number,Number2,Emailaddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote,Activity,ActivityTime) 
  VALUES(@ID,@CompanyName,@PersonName,@Designation,@Number,@Number2,@Emailaddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote,'New Record Added',getdate()) 

END

在这里，我使用@ID参数在表 LeadMastersNew 中查找新插入的ProspectCode，这些将在表 LoggerLeadMasters 中用作ProspectCode。

相关文章：