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
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
SELECT @errorDesc = 'Invalid ID'
BEGIN
RAISERROR(@errorDesc, 16, 1)
RETURN
END
IF EXISTS(SELECT 1 FROM Production.Culture AS pc WHERE PC.CultureID = @id)
SELECT @errorDesc = 'ID already exists'
BEGIN
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!
Aucun commentaire:
Enregistrer un commentaire