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