Stored procedure will not 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 

The method I call: -

 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; } } 

TitleID is now a foreign key in many tables. If a TitleID is used in any table entry, then it throws this exception, which says "Foreign key violation n". In my Stored Procedure above, I select zero in the else block if deletion fails. When delete IS is successful, it returns the TitleID value as 50, 99, or whatever. Now, what happens when the deletion fails, it does not return zero. I wanted the message to be displayed on the screen based on this null value returned by the Delete Saved method, but when it did not return any value (if the deletion failed), I returned zero in the catch block of my DeleteTitle () method.

Now I have two questions: -

• Why didn't the stored procedure return zero when the delete failed?
• Returns zero in a catch block, as I did above the correct path? I did not know how you extract the Exception number of the foreign key and so on, so I just went back there in catch catch.

Answer 1

You will need TRY ... CATCH in your procedure, not IF ... ELSE.

If you think about it, you are already in the IF part of your statement when DELETE fails with a foreign key violation. How can your code go to the ELSE block?

Answer 2

The problem is that the if statement will not execute the ELSE statement if it completes with an exception. Your IF statement also looks incorrect - should they not be EX EXISTS, [then delete the record?] As it is written now, if the record exists, it will NOT be deleted.

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

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

Answer 3

If Tables is a set of tables, you will need another [0] for the first column of the first table.

Answer 4

use this:

 CREATE PROCEDURE DeteleTitle ( @TitleID int ) AS BEGIN TRY DELETE FROM Titles WHERE TitleID=@TitleID SELECT CASE WHEN @@ROWCOUNT>0 THEN @TitleID ELSE 0 --row did not exist END END TRY BEGIN CATCH SELECT 0 --delete failed END CATCH go 

When several tables are “linked” using foreign keys, and you delete the parent row, you get an error, as you report, because the child data cannot exist without a parent. You may want to view cascading deletions or add code to this procedure to delete from the tables associated with the captions using foreign keys. Add these deletions before DELETE FROM Titles . do it like this:

 CREATE PROCEDURE DeteleTitle ( @TitleID int ) AS BEGIN TRY BEGIN TRANSACTION DELETE FROM YourOtherTablesA WHERE TitleID=@TitleID DELETE FROM YourOtherTablesB WHERE TitleID=@TitleID DELETE FROM Titles WHERE TitleID=@TitleID SELECT CASE WHEN @@ROWCOUNT>0 THEN @TitleID ELSE 0 --row did not exist END COMMIT END TRY BEGIN CATCH IF XACT_STATE()!=0 BEGIN ROLLBACK TRANSACTION END SELECT 0 --delete failed END CATCH go 

Answer 5

You can use @@ ERROR for the output result. @@ ERROR = 0 means a successful yet unsuccessful operation.

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