• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

SQL Server 2005 BEGIN CATCH

Hi I want to convert this @@ERROR Stored proc to SQL Server 2005 using BEGIN TRY Error handling
CREATE PROCEDURE
[dbo].[Proc_UpdateEmployeet]
( @EmpID            INT,
  @Name VARCHAR(256),
 @descriptionVARCHAR(MAX),
 @successID INT OUTPUT
)
AS
declare @ErrorVar int;
declare @RowCountVar int;
declare @empID int


  SELECT @empid=EmployeeID  FROM Employee    WHERE  UPPER(Name)=UPPER(@Name)
 BEGIN

 IF(@empID IS NOT null)  --- Name already exists return Output Param
      BEGIN
         SET @successID=-1
         RETURN
      END

 END

BEGIN
 UPDATE Employee
   SET Name=@Name,
    Description=@description
    WHERE EmpID=@empID



  SELECT @ErrorVar=@@ERROR,
@RowCountVar=@@ROWCOUNT

  IF(@ErrorVar <> 0)
  BEGIN                  
   SET @successID=-101
   RETURN
  END
     ELSE
     BEGIN
        SET @successID=@RowCountVar  --Number of Rows Updated
    END
 END
0
dotnet0824
Asked:
dotnet0824
  • 7
  • 6
  • 5
1 Solution
 
imitchieCommented:

BEGIN TRY
  UPDATE Employee SET
    Name=@Name,
    Description=@description
  WHERE EmpID=@empID
 
  SELECT @RowCountVar=@@ROWCOUNT
 
  SET @successID=@RowCountVar  --Number of Rows Updated
END TRY
BEGIN CATCH
  SET @successID=-101
  --RETURN -- not needed if this is end of procedure anyway
END CATCH

Open in new window

0
 
imitchieCommented:
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

declare @ErrorVar int;
declare @RowCountVar int;
 
IF EXISTS (SELECT 1 FROM employee WHERE NAME = @Name )
BEGIN 
	SET @successID=-1
	RETURN 
END 
 
BEGIN
	UPDATE Employee
	SET Name=@Name,
     	    Description=@description
	WHERE EmpID=@empID
	
	SELECT @ErrorVar=@@ERROR, @RowCountVar=@@ROWCOUNT 
	
	IF(@ErrorVar <> 0) OR @RowCountVar =0 
	BEGIN                  
		SET @successID=-101
		RETURN 
	END 
	ELSE
	BEGIN
		SET @successID=@RowCountVar  --Number of Rows Updated
	END 
 END 

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dotnet0824Author Commented:
Hi Imitchie.... You have missed the top code which checks if empID exists...
Can we integrate Begin Catch from top to the bottom of the stored proc

declare @ErrorVar int;
declare @RowCountVar int;
declare @empID int


  SELECT @empid=EmployeeID  FROM Employee    WHERE  UPPER(Name)=UPPER(@Name)
 BEGIN

 IF(@empID IS NOT null)  --- Name already exists return Output Param
      BEGIN
         SET @successID=-1
         RETURN
      END

 END

BEGIN
 UPDATE Employee
   SET Name=@Name,
    Description=@description
    WHERE EmpID=@empID



  SELECT @ErrorVar=@@ERROR,
@RowCountVar=@@ROWCOUNT

  IF(@ErrorVar <> 0)
  BEGIN                  
   SET @successID=-101
   RETURN
  END
     ELSE
     BEGIN
        SET @successID=@RowCountVar  --Number of Rows Updated
    END
 END
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

CREATE PROCEDURE [dbo].[Proc_UpdateEmployeet]
 @EmpID            INT,
  @Name VARCHAR(256),
 @description VARCHAR(MAX),
 @successID INT OUTPUT
 
AS
declare @ErrorVar int;
declare @RowCountVar int;
 
IF EXISTS (SELECT 1 FROM employee WHERE NAME = @Name )
BEGIN 
	SET @successID=-1
	RETURN 
END 
 
BEGIN TRY
	UPDATE Employee
	SET Name=@Name,
     	    Description=@description
	WHERE EmpID=@empID
	
	SELECT @ErrorVar=@@ERROR, @RowCountVar=@@ROWCOUNT 
	
	IF(@ErrorVar <> 0) OR @RowCountVar =0 
	BEGIN                  
		SET @successID=-201
		RETURN 
	END 
	ELSE
	BEGIN
		SET @successID=@RowCountVar  --Number of Rows Updated
	END 
END TRY
BEGIN CATCH
	SET @successID= -202
	
END CATCH

Open in new window

0
 
dotnet0824Author Commented:
But your are still using  @ERROR <> 0 and you have @successID = -201... Control woudlnt go to Begin CAtch Set @successID = -202
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Opps that was a mistake
replace this
>IF(@ErrorVar <> 0) OR @RowCountVar =0


IF @RowCountVar =0
the above will check whether any rows are updated, in case it didn't update any records, throw -201  and in case of any other errors it throws -201.

While selecting the return values make sure that you take values below " -200 " as sql server uses the values from 0 throw -200


0
 
dotnet0824Author Commented:
U mean that if @RowCountVar=0  we return -201 (an Error to the calling function).... What if  value passed from the client  lets say EMPID=30 Doesnot exist in the Database .. As per ur logic we have to throw error.. We need to throw a sensible message which says "Emp ID doesnot exist"
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In case the EmpID already exists, we are setting the @SuccessID as  "-1 " , and if you need to display a valid error , you can of course do this from the front end.
0
 
dotnet0824Author Commented:
Ok My last doubt is :
In our BEGIN CATCH
      SET @successID= -202
      
END CATCH
Is it good to Set Output Parameters and catch them at client and say "ERROR OCCURED IN STORED PROC" Or is there any other good way of handling Error if occured to let the client catch
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
This will be more than enough :)
0
 
dotnet0824Author Commented:
but if we return output parameter = -202 @ the client we say "Error Occoured in stored proc".. How woud we know what kind of Error it is... No way we can know right!!!!!!!!!!!!!!!!!!!!!!!
0
 
imitchieCommented:
results:
-1: name already exists
0 or positive number: rows updated
-other: SQL internal error number
BEGIN TRY
  declare @ErrorVar int;
  declare @RowCountVar int;
  declare @empID int
 
  SELECT @empid=EmployeeID  FROM Employee    
  WHERE  UPPER(Name)=UPPER(@Name)
 
  IF(@empID IS NOT null)  --- Name already exists return Output Param
    RAISERROR(0)
 
  UPDATE Employee SET
    Name=@Name,
    Description=@description
  WHERE EmpID=@empID
 
  SELECT @RowCountVar=@@ROWCOUNT
 
  SET @successID=@RowCountVar  --Number of Rows Updated
END TRY
BEGIN CATCH
  SET @successID= -ERROR_NUMBER()
  --RETURN -- not needed if this is end of procedure anyway
END CATCH

Open in new window

0
 
imitchieCommented:
BEGIN TRY
  declare @ErrorVar int;
  declare @RowCountVar int;
  declare @empID int
 
  SELECT @empid=EmployeeID  FROM Employee    
  WHERE  UPPER(Name)=UPPER(@Name)
 
  IF(@empID IS NOT null)  --- Name already exists return Output Param
    RAISERROR(1)   --- gets flipped to -1 by -ERROR_NUMBER() below
 
  UPDATE Employee SET
    Name=@Name,
    Description=@description
  WHERE EmpID=@empID
 
  SELECT @RowCountVar=@@ROWCOUNT
 
  SET @successID=@RowCountVar  --Number of Rows Updated
END TRY
BEGIN CATCH
  SET @successID= -ERROR_NUMBER()
  --RETURN -- not needed if this is end of procedure anyway
END CATCH
0
 
imitchieCommented:
sql server 2005 is more elegant with try.. catch. time to move forward from @@error + return blocks everywhere.
0
 
dotnet0824Author Commented:
Hi Mitchie,
Is it not Good to have like this
BEGIN CATCH
RAISERROR(ERROR_MESSAGE,16,1)
END CATCH
This would throw the exact Error to the client right.........Foreign key violation or What ever TSQL Error might be....How would we know  if we send Output Parameter = ERROR_NUMBER... We wouldnt know what exactly the error is right
0
 
imitchieCommented:
yes but you were after a conversion from SQL 2000/7 code to SQL 2005 try-catch pattern. the old code did not use RAISERROR to cause the client to handle query errors. the client only had to handle error _codes_ returned, one of -1, -101, or rows affected (0 or positive).

the client can have a reference table for message to number
if you were going to raise the error back to the client, then you wouldn't need TRY-CATCH would you?
-- from MS code, shows
-- ERROR_MESSAGE: Divide by zero error encountered.
-- for ERROR_NUMBER: 8134
 
BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

Open in new window

0
 
dotnet0824Author Commented:
What i understand is this from ur statement:
In SQL Server 2000 We couldnt use RAISERROR in Stored procedures to throw back error to the client. We could have only return values being sent to the client ? Is that right

Now Raiserror can be used in Begin Catch END catch blocks.. Am i right?
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now