CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Maha
Maha

Reputation: 19

Error Handling in T-SQL

I am newbie in SQL Server 2008 and have question to clarify.

I have created small stored procedure and use error handling method to raise an error.

Try block is working fine but catch is not.

I created a stored procedure like this:

USE [AdventureWorks2008R2]
GO
CREATE PROCEDURE [dbo].[uspGetpersonName] 
    @BusinessEntityID int 
AS
BEGIN
BEGIN TRY 
    SELECT FirstName, LastName, MiddleName
    FROM Person.Person
    WHERE BusinessEntityID = @BusinessEntityID;
END TRY
BEGIN CATCH
    RAISERROR('There is no record Exixts!!!',16,1) 
END CATCH   
END

Execute the stored procedure in another query builder like this:

EXEC dbo.uspGetpersonName 343545

Upvotes: 1

Views: 163

Answers (1)

Bill Hurt
Bill Hurt

Reputation: 749

Having zero rows returned from a select statement is not an error. It's just the result.

If you were to run another statement in your try block that really did throw an error (test by deliberately dividing by zero SELECT 3/0), then your catch block would fire.

What you want is more like this:

SELECT FirstName, LastName, MiddleName
FROM Person.Person
WHERE BusinessEntityID = @BusinessEntityID;

IF @@rowcount = 0
 BEGIN
   RAISERROR('There is no record Exixts!!!',16,1)
 END

Upvotes: 5

Related Questions