Mixing
SQL - want to write unique error value per IF condition
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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?
SQL Server Management Studio 17.2
sql
sql-server if-statement error-handling condition edited Jan 3 at 11:38
GMB
22k61128
asked Jan 3 at 11:18
Cathie HeartCathie Heart
12
add a comment |
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?
SQL Server Management Studio 17.2
sql
sql-server if-statement error-handling condition edited Jan 3 at 11:38
GMB
22k61128
asked Jan 3 at 11:18
Cathie HeartCathie Heart
12
Which dbms are you using? (That code is product specific.)
– jarlh
Jan 3 at 11:22
I am using SQL Server Management Studio 17.2
– Cathie Heart
Jan 3 at 11:34
Is anyone able to help me with this? I'm looking for a better more condense error handling format.
– Cathie Heart
Jan 17 at 10:56
add a comment |
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?
SQL Server Management Studio 17.2
sql
sql-server if-statement error-handling condition edited Jan 3 at 11:38
GMB
22k61128
asked Jan 3 at 11:18
Cathie HeartCathie Heart
12
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?
SQL Server Management Studio 17.2
sql
sql-server if-statement error-handling condition sql
sql-server if-statement error-handling condition edited Jan 3 at 11:38
GMB
22k61128
asked Jan 3 at 11:18
Cathie HeartCathie Heart
12
edited Jan 3 at 11:38
GMB
22k61128
asked Jan 3 at 11:18
Cathie HeartCathie Heart
12
edited Jan 3 at 11:38
GMB
22k61128
edited Jan 3 at 11:38
GMB
22k61128
edited Jan 3 at 11:38
GMB
22k61128
22k61128
asked Jan 3 at 11:18
Cathie HeartCathie Heart
12
asked Jan 3 at 11:18
Cathie HeartCathie Heart
12
asked Jan 3 at 11:18
Cathie HeartCathie Heart
12
12
Which dbms are you using? (That code is product specific.)
– jarlh
Jan 3 at 11:22
I am using SQL Server Management Studio 17.2
– Cathie Heart
Jan 3 at 11:34
Is anyone able to help me with this? I'm looking for a better more condense error handling format.
– Cathie Heart
Jan 17 at 10:56
add a comment |
Which dbms are you using? (That code is product specific.)
– jarlh
Jan 3 at 11:22
I am using SQL Server Management Studio 17.2
– Cathie Heart
Jan 3 at 11:34
Is anyone able to help me with this? I'm looking for a better more condense error handling format.
– Cathie Heart
Jan 17 at 10:56
Which dbms are you using? (That code is product specific.)
– jarlh
Jan 3 at 11:22
Which dbms are you using? (That code is product specific.)
– jarlh
Jan 3 at 11:22
I am using SQL Server Management Studio 17.2
– Cathie Heart
Jan 3 at 11:34
I am using SQL Server Management Studio 17.2
– Cathie Heart
Jan 3 at 11:34
Is anyone able to help me with this? I'm looking for a better more condense error handling format.
– Cathie Heart
Jan 17 at 10:56
Is anyone able to help me with this? I'm looking for a better more condense error handling format.
– Cathie Heart
Jan 17 at 10:56
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021280%2fsql-want-to-write-unique-error-value-per-if-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021280%2fsql-want-to-write-unique-error-value-per-if-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Which dbms are you using? (That code is product specific.)
– jarlh
Jan 3 at 11:22
I am using SQL Server Management Studio 17.2
– Cathie Heart
Jan 3 at 11:34
Is anyone able to help me with this? I'm looking for a better more condense error handling format.
– Cathie Heart
Jan 17 at 10:56