Solved

SQL Server 2005 BEGIN CATCH

Posted on 2007-11-17
18
257 Views
Last Modified: 2008-02-01
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
Comment
Question by:dotnet0824
  • 7
  • 6
  • 5
18 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20304012

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
 
LVL 25

Expert Comment

by:imitchie
ID: 20304013
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20304037

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:dotnet0824
ID: 20304070
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20304074

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
 

Author Comment

by:dotnet0824
ID: 20304282
But your are still using  @ERROR <> 0 and you have @successID = -201... Control woudlnt go to Begin CAtch Set @successID = -202
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20304302
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
 

Author Comment

by:dotnet0824
ID: 20305126
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20306316
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
 

Author Comment

by:dotnet0824
ID: 20307041
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20307071
This will be more than enough :)
0
 

Author Comment

by:dotnet0824
ID: 20309488
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20309559
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20309561
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20309565
sql server 2005 is more elegant with try.. catch. time to move forward from @@error + return blocks everywhere.
0
 

Author Comment

by:dotnet0824
ID: 20309569
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
 
LVL 25

Accepted Solution

by:
imitchie earned 400 total points
ID: 20309590
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
 

Author Comment

by:dotnet0824
ID: 20310989
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question