Solved

SQL Server 2005 BEGIN CATCH

Posted on 2007-11-17
18
251 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
Comment Utility

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
Comment Utility
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility

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
 

Author Comment

by:dotnet0824
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:dotnet0824
Comment Utility
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
Comment Utility
This will be more than enough :)
0
 

Author Comment

by:dotnet0824
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
sql server 2005 is more elegant with try.. catch. time to move forward from @@error + return blocks everywhere.
0
 

Author Comment

by:dotnet0824
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 43
Help with checking if excel exist in client's computer 9 36
ms sql stored procedure 22 76
separate column 24 19
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now