T-SQL Basic custom errors get ignored in stored procedure

Question

EDIT3: SOLUTION Apparently my code had problems with the BEGIN / END blocks, as the users that replied suggested (thank you!)

Still, I couldn't get it you work, until I found this post and tried this if/else structure: using Switch like logic in T-SQL

---

EDIT2:
I've written a couple PRINTs in the procedure and the values are passing just right. Now it only says 'Invalid ID', even while sending 200.

220
dsf4
Oct 12 2018 10:10AM
Msg 50000, Level 16, State 1, Procedure p_ValCulture, Line 188
Invalid ID

---

EDIT: I've tried setting the error message inside BEGIN / END; it didn't work. This is the code for that part.

IF @operacion LIKE 'I'
    BEGIN
        IF @id IS NULL OR @id <= 0
            BEGIN
                SELECT @errorDesc = 'Invalid ID'    
                RAISERROR(@errorDesc, 16, 1)
            END

        IF EXISTS(SELECT 1 FROM Production.Culture AS pc WHERE PC.CultureID = @id)
            BEGIN   
                SELECT @errorDesc = 'ID already exists'
                RAISERROR(@errorDesc, 16, 1)
            END
END

I get this if I execute the stored procedure

Msg 50000, Level 16, State 1, Procedure p_ValCulture4, Line 183

Below that message there is a blank space, the value of an error message that I couldn't set.

These are the parameters I pass:

GO
EXEC p_ValCulture4 200, 'John', '10-12-2018 10:10:10.000','I'

I'm using SQL Server Management 2014, on Windows 64bits.

---

I'm trying to catch up on some T-SQL I didn't get enough attention.. but I'm totally stuck :/ I've checked Google, Youtube, SO, etc.. and tried many things but nothing works and I'm wasting time totally stuck on this part that I can't understand.

I have a stored procedure that when sent an 'operation' as 'I' (char) will perform an insert into a table. I'm using the AdventureWorks2014 database as practise.

The problem is that I want to send different error messages if the id sent is null, or another one if it already exists in the table, etc. This is the code for the procedure:

CREATE PROCEDURE p_ValCulture4
   @id INT,
   @name NVARCHAR(50),
   @date DATETIME,
   @operacion CHAR(1)
AS
BEGIN
   print @id
   print @name
   print @date
   SET NOCOUNT ON 
   DECLARE @errorDesc nvarchar(MAX)

   BEGIN TRY

       SELECT @operacion = UPPER(@operacion) 

       IF @operacion = 'I'  /* the Insert option */
          BEGIN
              IF @id IS NULL OR @id <= 0
                  BEGIN
                      SELECT @errorDesc = 'Invalid ID'  
                      RAISERROR(@errorDesc, 16, 1)
                  END

              IF EXISTS(SELECT 1 FROM Production.Culture AS pc WHERE PC.CultureID = @id)
                 BEGIN
                      SELECT @errorDesc = 'ID already exists'
                      RAISERROR(@errorDesc, 16, 1)
                 END

              SELECT @errorDesc = 'ERROR: Insert error'
              INSERT INTO Production.Culture VALUES
        (@id, @name, @date);
              SELECT 'Rows: ' + CONVERT(VARCHAR(10),@@ROWCOUNT)
          END
      END TRY
   BEGIN CATCH
       RAISERROR (@errorDesc,16,1)
   END CATCH
END

The first IF, if I send id = null works fine, I get the right error; but if I send an existing id, the IF get completely ignored. The same happens with the insert, it works fine, but only if there are no IFs in the procedure..

I can't get my mind how these IF - BEGIN / END work.. and why it can only read the first IF but ignores subsequent ones..

I've tried putting everything inside an IF - BEGIN / END and then ELSE - BEGIN / END, same results.

I've tried setting the error message inside de IFs, and also outside. Also, inside the IFs, but before BEGIN. I've tried writing the error directly inside RAISERROR, same results.

If anyone can help me understand why the IFs get ignored and the logic behind THESE IFs in T-SQL, it would be PRETTY much appreciated :) Thank you!

Answer 1

Consistent indenting will definitely help you see issues related to your Begin End pairs.

Your first IF test has the BEGIN on the same horizontal position below it, but the next IF test has its BEGIN indented further.

If you make this IF test like the first, in that the BEGIN & END sit on the same horizontal position, it will expose issues like ZLK points out in the above comment, SELECT @errorDesc = 'Invalid ID' is running outside the BEGIN END pair.