Click here to Skip to main content
15,881,789 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Why error messages are not returning code behind in my Stored Procedure ? I am able to print the final error message. But on setting the error message as shown below in code behind of asp.net its coming as null.

SQL
ALTER PROCEDURE [dbo].[VALIDATE_MASS_EMPDATA_UPDATION]    
(    
 @corporateId    VARCHAR(20),     
 @empId                  VARCHAR(20),     
 @citizenId                  VARCHAR(15),    
    @cardNo      VARCHAR(19),    
 @p_error_code  INT    OUTPUT,      
 @p_error_msg  VARCHAR(1000) OUTPUT 
   
)    
AS    
BEGIN    
 SET  NOCOUNT ON    
 DECLARE @error    VARCHAR(40)    
DECLARE @C3EMPREGID BIGINT    
SET @C3EMPREGID=0    
 --Added by Alex on Feb 7,2014 to retrieve correct card no as masked is now passed as parameter in Excel    
--  SELECT @C3EMPREGID=C3EMPREGID FROM EMPLOYEE     
--  WHERE EmpId=@empId     
--  AND CitizenId=@citizenId      
--  AND right(rtrim(@cardNo),4)=right(rtrim(CardNo),4)    
--  AND CorporateId=@corporateId     
--  AND Isdeleted=0      
       
--print @C3EMPREGID     
--Added by Alex in where clause with primary key @C3EMPREGID     
   IF Not EXISTS (SELECT EmpId FROM dbo.Employee WHERE EmpId=@empId and CorporateId=@corporateId )  
   BEGIN  
   SET @p_error_code='500'  
    IF(@p_error_msg !='')
   SET @p_error_msg = @p_error_msg 
   SET @p_error_msg=@p_error_msg + 'Invalid employee. :'  
     
   --return(1)  
   END  
   IF not EXISTS (SELECT CardNo FROM dbo.Employee WHERE EmpId=@empId and CorporateId=@corporateId and right(rtrim(@cardNo),4)=right(rtrim(CardNo),4))  
   BEGIN  
   SET @p_error_code='200'  
   IF(@p_error_msg !='')
   SET @p_error_msg = @p_error_msg
   SET @p_error_msg = @p_error_msg + ' Invalid Card Number : '  
   --return(1)  
   END    
   
   --Added by Alex for Invalid Citizen Id in where clause with Citizen Id    
   IF Not EXISTS (SELECT CitizenId FROM dbo.Employee WHERE EmpId=@empId and CorporateId=@corporateId  and CitizenId= @citizenId)    
   BEGIN    
  
   SET @p_error_code='300'    
      IF(@p_error_msg !='')
   SET @p_error_msg = @p_error_msg 
   SET @p_error_msg = @p_error_msg + ' Invalid Citizen Id. :'    
   --return(1)     
   END    
     
     
     if @p_error_code<>'0'
      BEGIN    
     --print @p_error_msg
     SET @p_error_msg =@p_error_msg
     return(1)
         
   END
Posted
Updated 20-May-14 0:16am
v4
Comments
Bh@gyesh 20-May-14 5:00am    
From your code it seems that you have not written select statement at end. i.e. if you want to select error message, then you need select statement.

Add following line at before END :

SELECT @p_error_msg

Regards,
Bhagyesh
Nandakishore G N 20-May-14 5:35am    
What have you done in your code behind? ..paste it
Ravi Shankar Dokka 20-May-14 6:02am    
Could you share the ASP.Net code?
CHill60 20-May-14 6:21am    
Need to see the code behind

1 solution

The purpose of SQL query is to select some data from the database and send it to some client. You SP does nothing!
You declare some local variable, set it value and exit.
You have to add some select to your SP, like:
SQL
SELECT @p_error_msg

However the exact solution depends on your need.
One more thing! Lines like these, make suspicious about your knowledge and understanding of SQL. These lines do NOTHING!!!
SQL
if @p_error_code<>'0'
BEGIN    
  --print @p_error_msg
  SET @p_error_msg =@p_error_msg
END
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900