CODEWITHSUNDEEP
sql-serverstored-procedures
Subodh
Subodh

Reputation: 11

Can't insert a second row into a table though it insert first row by stored procedure

It inserted a first row successfully but it's not inserting any other row, though second row has no conflict of primary key

Code in my aspx.cs file:

 outputParVal = sqlCmd.Parameters[outputParName].Value;

outparameter in stored procedure is--- "Result" 

CREATE PROCEDURE [dbo].[RecruiterProfileInsert]  
  @CompanyId int,     
  @CompanyName varchar(200),    
  @EmailId   varchar(50) ,    
  @Password  varchar(20) ,    
  @ContactNumber varchar(15),    
  @Website  varchar(50),    
  @CompanyProfile varchar(2000),   
  @IsVerified bit,   
  @Result Tinyint OutPut  
  --@CreatedDate  datetime ,    
  --UpdatedDate datetime         
AS  
BEGIN        
    -- Insert statements for procedure here          
 --check whether @CompanyName already exist or not if exist then return           
IF EXISTS(SELECT Top 1 * FROM  RecruiterProfile WHERE @CompanyId = LTRIM(RTRIM(@CompanyId)))        
 BEGIN          
  SET @Result = 0-- Already Exists          
 END          
ELSE          
BEGIN              
 INSERT INTO RecruiterProfile        
(    
  CompanyId,    
  CompanyName,    
  EmailId ,    
  Password ,    
  ContactNumber,    
  Website ,    
  CompanyProfile ,  
  IsVerified,    
  CreatedDate    
)        
 VALUES        
(    
@CompanyId,    
@CompanyName,        
@EmailId ,         
@Password,      
@ContactNumber,          
@Website,       
@CompanyProfile,  
@IsVerified,          
GetDate()  
)        
set @Result =1     
 return                
 END   
END

Upvotes: 1

Views: 177

Answers (1)

ErikE
ErikE

Reputation: 50211

This is the problem:

SELECT Top 1 * FROM  RecruiterProfile WHERE @CompanyId = LTRIM(RTRIM(@CompanyId))

This inherently makes no sense. You're comparing the variable to itself. Take the @ sign out of one of the CompanyId references. The RTrim is unnecessary in SQL Server, and the LTrim doesn't make sense either because the later insert doesn't also LTrim so something is going to go wrong eventually.

Furthermore, inside of an EXISTS clause, TOP makes no sense unless you are using ORDER BY and doing something with the final result. Just do SELECT * inside of EXISTS clauses.

One more thing: if there is high concurrency and users could possibly try to insert the same thing at the same time, your query could still fail on a duplicate key violation.

Upvotes: 2

Related Questions