jeudi 3 janvier 2019

SQL - want to write unique error value per IF condition

I'm handing lots of data and error checking it to ensure that it follows a correct format. So I'm creating a lot of IF conditions and so far I can group lots of conditions but only output one kind of error .. or i end up writing a heck of a lot of code per condition, just to have a crystal clear error output.

Is there a way to put into the IF condition a specific error output string?

Here is my code:

-- set the initial @IntHosp_SUM value
        SELECT @IntHosp_SUM = 0  

        -- Store the sum value to @IntHosp_SUM
        SELECT  @IntHosp_SUM =
            (
            (COALESCE (CASE WHEN @Int1_5hosp = 1 THEN COUNT (Int1_5) ELSE NULL END, 0)) + 
            (COALESCE (CASE WHEN @Int2_5hosp = 1 THEN COUNT (Int2_5) ELSE NULL END, 0)) + 
            (COALESCE (CASE WHEN @Int3_5hosp = 1 THEN COUNT (Int3_5) ELSE NULL END, 0)) + 
            (COALESCE (CASE WHEN @Int4_5hosp = 1 THEN COUNT (Int4_5) ELSE NULL END, 0)) + 
            (COALESCE (CASE WHEN @Int5_5hosp = 1 THEN COUNT (Int5_5) ELSE NULL END, 0)) + 
            (COALESCE (CASE WHEN @Int6_5hosp = 1 THEN COUNT (Int6_5) ELSE NULL END, 0))  
            ) 
        FROM    ASSSIST2_M0Teacher


        -- Check  RESUSE_SUM Value against Intervention Entries
        SELECT  @ErrorID = IsNull(Max(ErrorID) + 1, 1)
        FROM        ErrorTemp
        WHERE   (Quest = @DB + @Quest) AND (ValidateID = @ValidateID) 

        SELECT @Error = 0

        IF (@IntHosp_SUM = 1 and @Hosp1_info is null )
        or (@IntHosp_SUM = 2 and (@Hosp1_info is null or @Hosp2_info is null))
        or (@IntHosp_SUM >=3 and (@Hosp1_info is null or @Hosp2_info is null or @Hosp3_info is null))


        or (@IntHosp_SUM = 0 and (@Hosp1_info is not null or @Hosp2_info is not null or @Hosp3_info is not null ))
        or (@IntHosp_SUM = 1 and (@Hosp2_info is not null or @Hosp3_info is not null ))
        or (@IntHosp_SUM >= 2 and @Hosp3_info is not null)



            BEGIN   
            SELECT @Error = 1   
            END
            BEGIN 
            IF @Error = 1   
                INSERT INTO     ErrorTemp 
                        (Quest, 
                        ValidateID, 
                        ErrorID, 
                        ErrorType,
                        ErrorDesc,
                        Field)
                VALUES  (@DB + @Quest, 
                        @ValidateID, 
                        @ErrorID,
                        'Validation',       
                        'Too much or no info present, so incorrect',                                
                        'Hosp Info - admissions')                                       
            SELECT  @ErrorID = @ErrorID + 1
            END

I have declared all variables and where they are in the DB, but didn't want to clutter with extra code. You can see that I sum a series of fields if they contain the value 1 .. I don't want to add any other values, just those with '1'.

My IF conditions then check if that if there is a sum of 1, there is one entry. If 2, then two entries .. we have to be thorough and check the inverse of that, so if the sum value is 0, then no entries are made and so forth.

Is there a way to pass a string from the IF / OR condition to the error output so that I can write a unique error per condition?

Aucun commentaire:

Enregistrer un commentaire