Stored procedure won't return 0

Question

this is my sp

CREATE PROCEDURE DeteleTitle
(
@TitleID int
)
AS
BEGIN
IF EXISTS(Select TitleID from Titles WHERE TitleID=@TitleID)
BEGIN
DELETE FROM Titles WHERE  TitleID=@TitleID
SELECT @TitleID
END
ELSE
BEGIN
SELECT 0
END
END

Method where I am calling it is:-

public Int32 DeleteTitle(Int32 TitleID)
{
try
{
int ds=0;
SqlParameter[] sqlparam=new SqlParameter[1];
sqlparam[0]=(@TitleID,TitleID);
ds=Convert.ToInt32(SqlHelper.ExecuteScalar(ConfigurationManager.ConnectionStrings["con"].ConnectionString,CommandType.StoredProcedure,"DeleteTitle",sqlparam).Tables[0]);
return ds;
}

catch(Exception ex)
{
return 0;
}
}

Now TitleID is a Foreign Key in many Tables. If some Table's record is using TitleID, then it throws this exception that says "Foreign Key Violation n stuff". In my Stored procedure above, I am selecting zero in the else block in case delete is not successful. When delete IS successful, it returns TitleID's value like 50, 99 or whatever. Now what is happening is , when delete is not successful, it is not returning zero. I wanted a message to be displayed on screen based on this zero value returned by Delete Stored procedure but when it didn't return any value (when delete failed), I returned zero in the catch block of my DeleteTitle() method.

Now I have two questions:-

1. Why did the Stored procedure not return zero when delete failed?
2. Is returning zero in the catch block like I have done above the right way? I didn't know how you retrieve Foreign key Exception number and stuff so I just returned zero there in catch block.

Answer 1

You'd want a TRY...CATCH in your procedure, not an IF...ELSE.

If you think about it, you're already in the IF portion of your statement when the DELETE fails with the foreign key violation. How could your code possibly jump to the ELSE block?

Answer 2

The problem is that your if statement will not execute the ELSE statement if it fails with an exception. Your IF statement also appears incorrect- shouldn't it be IF EXISTS, [then delete record?] The way it's written now, if the record exists it will NOT be deleted.

The extended problem is that it is considered bad practice to rely on an exception (in C#, SQL, or any other language) as a method for flow control.

You would be better off explicitly checking for related records by using the EXISTS statement for each related table.