为什么错误消息不会在我的存储过程中返回代码？ [英] Why error messages are not returning code behind in my Stored Procedure ?

问题描述

为什么错误消息不会在我的存储过程中返回代码？我能够打印最终的错误消息。但是在设置错误消息时，如下面的asp.net代码所示，它将变为null。

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.

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

推荐答案

SQL查询的目的是从数据库中选择一些数据并将其发送到某个客户端。你SP什么都不做！

你声明一些局部变量，设置它值并退出。

你必须在你的SP中添加一些选择，例如：

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:

SELECT @p_error_msg

但是确切的解决方案取决于你的需要。

还有一件事！像这样的行，使你对你的SQL知识和理解产生怀疑。这些行没有任何结果!!!

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!!!

if @p_error_code<>'0'
BEGIN    
  --print @p_error_msg
  SET @p_error_msg =@p_error_msg
END

这篇关于为什么错误消息不会在我的存储过程中返回代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！