jeudi 30 juillet 2015

Nested if logic with Error Messaging in SQL

I have 6 integer fields (called genre1 ... genre6 respectively). User enters selects from a drop down on the front end. What I would like to happen is messaging to appear stating that he's selected the same values for fields 1 and 2 or 1 and 3 etc.

It looks like this.

genre1 int,
genre2 int,
genre3 int,
genre4 int,
genre5 int,
genre6 int,

if genre1 = genre2
or genre1 = genre3
or genre1 = genre4
or genre1 = genre5
or genre1 = genre6
or genre2 = genre3
or genre2 = genre4
or genre2 = genre5
etc ... 

I don't need to state the combination of fields that he's entered incorrectly only that he's done so and he should review the data before continuing.

This has to happen in a trigger (insert and update) on the table. There is other logic being tested and evaluated within the trigger as well but at the end I've added the following logic to start and it doesn't seem to work.

declare @genre1 int
set @genre1 = (select genre1 from lt_wmi_men_org_cs where   id_key = @id_key) 
-- select * from lt_wmi_men_org_cs

BEGIN TRY
        IF (@genre1 = 6) -- This is used to test a particular value. 
        BEGIN
            Select @genre1
        END
END TRY
BEGIN CATCH
   --  SELECT ERROR_MESSAGE() + @genre1 AS ErrorMessage;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    PRINT ERROR_MESSAGE() + @genre1;
    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

END CATCH; 

I've also tried a more complex if statement but none of it seems to be working.

BEGIN TRY
        IF (@genre1 = @genre2 OR  @genre1 = @genre3 OR  @genre1 = @genre4 OR  @genre1 = @genre5 OR  @genre1 = @genre6 )
        BEGIN
            Select @genre1
        END
END TRY

Please advise.
Thank you

Aucun commentaire:

Enregistrer un commentaire