Mixing
Unable to ALTER TABLE inside CASE WHEN within Stored Procedure
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I'm trying to do a fairly simple Stored Procedure on MySQL, but it keeps throwing errors.
There are additional WHEN conditions, but I've removed them here to keep it simple, and this simpler version doesn't work either. I get SQL Error 1064 in Statement #2: You have an error in your SQL Syntax... near ELSE CALL finance.sprProce....
The finance.spProcessTrans_AddToLogs are fine, I use the exact syntax in a number of other SPs. The code that is processed between the WHENs is fine as I've tested it separately.
Version: Windows/MySQL 8.0.12 Community
Where am I going wrong? Should I be able to do this ALTER TABLE within a CASE WHEN?
DELIMITER //
DROP PROCEDURE IF EXISTS finance.spProcessIndex;
CREATE PROCEDURE finance.spProcessIndex(
IN tblName VARCHAR(50),
IN actDesc VARCHAR(50)
)
BEGIN
SET tblName = IFNULL(tblName, 'ERROR');
SET actDesc = IFNULL(actDesc, 'ERROR');
CASE
WHEN actDesc='CREATE' THEN
CASE
WHEN tblName='tbl_transactions' THEN
ALTER TABLE tbl_transactions
MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
ADD INDEX IDX_tbl_transactions_product (Product(25)),
ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
ELSE
ALTER TABLE tbl_transactions_tmp_worker_aa
MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
ADD INDEX IDX_tbl_transactions_product (Product(25)),
ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
END;
ELSE
CALL finance.spProcessTrans_AddToLogs('spProcessIndex','BREAKPOINT','ERROR: Bad ACTION',CONCAT('A bad ACTION was specified {',actDesc,'}'));
END;
END//
DELIMITER ;
mysql stored-procedures
asked Jan 3 at 9:44
aSystemOverloadaSystemOverload
1,311173962
add a comment |
0
I'm trying to do a fairly simple Stored Procedure on MySQL, but it keeps throwing errors.
There are additional WHEN conditions, but I've removed them here to keep it simple, and this simpler version doesn't work either. I get SQL Error 1064 in Statement #2: You have an error in your SQL Syntax... near ELSE CALL finance.sprProce....
The finance.spProcessTrans_AddToLogs are fine, I use the exact syntax in a number of other SPs. The code that is processed between the WHENs is fine as I've tested it separately.
Version: Windows/MySQL 8.0.12 Community
Where am I going wrong? Should I be able to do this ALTER TABLE within a CASE WHEN?
DELIMITER //
DROP PROCEDURE IF EXISTS finance.spProcessIndex;
CREATE PROCEDURE finance.spProcessIndex(
IN tblName VARCHAR(50),
IN actDesc VARCHAR(50)
)
BEGIN
SET tblName = IFNULL(tblName, 'ERROR');
SET actDesc = IFNULL(actDesc, 'ERROR');
CASE
WHEN actDesc='CREATE' THEN
CASE
WHEN tblName='tbl_transactions' THEN
ALTER TABLE tbl_transactions
MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
ADD INDEX IDX_tbl_transactions_product (Product(25)),
ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
ELSE
ALTER TABLE tbl_transactions_tmp_worker_aa
MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
ADD INDEX IDX_tbl_transactions_product (Product(25)),
ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
END;
ELSE
CALL finance.spProcessTrans_AddToLogs('spProcessIndex','BREAKPOINT','ERROR: Bad ACTION',CONCAT('A bad ACTION was specified {',actDesc,'}'));
END;
END//
DELIMITER ;
mysql stored-procedures
asked Jan 3 at 9:44
aSystemOverloadaSystemOverload
1,311173962
add a comment |
0
0
0
I'm trying to do a fairly simple Stored Procedure on MySQL, but it keeps throwing errors.
There are additional WHEN conditions, but I've removed them here to keep it simple, and this simpler version doesn't work either. I get SQL Error 1064 in Statement #2: You have an error in your SQL Syntax... near ELSE CALL finance.sprProce....
The finance.spProcessTrans_AddToLogs are fine, I use the exact syntax in a number of other SPs. The code that is processed between the WHENs is fine as I've tested it separately.
Version: Windows/MySQL 8.0.12 Community
Where am I going wrong? Should I be able to do this ALTER TABLE within a CASE WHEN?
DELIMITER //
DROP PROCEDURE IF EXISTS finance.spProcessIndex;
CREATE PROCEDURE finance.spProcessIndex(
IN tblName VARCHAR(50),
IN actDesc VARCHAR(50)
)
BEGIN
SET tblName = IFNULL(tblName, 'ERROR');
SET actDesc = IFNULL(actDesc, 'ERROR');
CASE
WHEN actDesc='CREATE' THEN
CASE
WHEN tblName='tbl_transactions' THEN
ALTER TABLE tbl_transactions
MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
ADD INDEX IDX_tbl_transactions_product (Product(25)),
ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
ELSE
ALTER TABLE tbl_transactions_tmp_worker_aa
MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
ADD INDEX IDX_tbl_transactions_product (Product(25)),
ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
END;
ELSE
CALL finance.spProcessTrans_AddToLogs('spProcessIndex','BREAKPOINT','ERROR: Bad ACTION',CONCAT('A bad ACTION was specified {',actDesc,'}'));
END;
END//
DELIMITER ;
mysql stored-procedures
asked Jan 3 at 9:44
aSystemOverloadaSystemOverload
1,311173962
I'm trying to do a fairly simple Stored Procedure on MySQL, but it keeps throwing errors.
There are additional WHEN conditions, but I've removed them here to keep it simple, and this simpler version doesn't work either. I get SQL Error 1064 in Statement #2: You have an error in your SQL Syntax... near ELSE CALL finance.sprProce....
The finance.spProcessTrans_AddToLogs are fine, I use the exact syntax in a number of other SPs. The code that is processed between the WHENs is fine as I've tested it separately.
Version: Windows/MySQL 8.0.12 Community
Where am I going wrong? Should I be able to do this ALTER TABLE within a CASE WHEN?
DELIMITER //
DROP PROCEDURE IF EXISTS finance.spProcessIndex;
CREATE PROCEDURE finance.spProcessIndex(
IN tblName VARCHAR(50),
IN actDesc VARCHAR(50)
)
BEGIN
SET tblName = IFNULL(tblName, 'ERROR');
SET actDesc = IFNULL(actDesc, 'ERROR');
CASE
WHEN actDesc='CREATE' THEN
CASE
WHEN tblName='tbl_transactions' THEN
ALTER TABLE tbl_transactions
MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
ADD INDEX IDX_tbl_transactions_product (Product(25)),
ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
ELSE
ALTER TABLE tbl_transactions_tmp_worker_aa
MODIFY TransactionID INT AUTO_INCREMENT PRIMARY KEY,
ADD INDEX IDX_ProcessTrans_A (CustomerRef, TransMonth, Product, TransValue, RowReference, TransactionID,ProdInCust_Mnth_Same_SameProd_LowerVal),
ADD INDEX IDX_tbl_transactions_product (Product(25)),
ADD INDEX IDX_tbl_transactions_prodval (Product, TransValue);
END;
ELSE
CALL finance.spProcessTrans_AddToLogs('spProcessIndex','BREAKPOINT','ERROR: Bad ACTION',CONCAT('A bad ACTION was specified {',actDesc,'}'));
END;
END//
DELIMITER ;
mysql stored-procedures
mysql stored-procedures
asked Jan 3 at 9:44
aSystemOverloadaSystemOverload
1,311173962
asked Jan 3 at 9:44
aSystemOverloadaSystemOverload
1,311173962
edited Jan 3 at 10:09
aSystemOverload
asked Jan 3 at 9:44
aSystemOverloadaSystemOverload
1,311173962
asked Jan 3 at 9:44
aSystemOverloadaSystemOverload
1,311173962
asked Jan 3 at 9:44
aSystemOverloadaSystemOverload
1,311173962
1,311173962
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Try:
DELIMITER //
-- DROP PROCEDURE IF EXISTS finance.spProcessIndex;
DROP PROCEDURE IF EXISTS finance.spProcessIndex//
.
.
.
CASE
WHEN actDesc='CREATE' THEN
.
.
.
CASE
WHEN tblName='tbl_transactions' THEN
.
.
.
END CASE;
END CASE;
.
.
.
DELIMITER ;
answered Jan 3 at 9:58
wchiquitowchiquito
12.1k22335
Ahhh, I didn't even consider it should be END CASE, as I've used just END in other SPs and it's been fine. Is there a set scenario when you should(n't) use END (CASE)? I've checked the documentation at dev.mysql.com/doc/refman/5.7/en/case.html but still unsure.
– aSystemOverload
Jan 3 at 10:08
@aSystemOverload: CASE expression, differs from the CASE statement, it is terminated withENDinstead ofEND CASE.
– wchiquito
Jan 3 at 10:13
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f54019720%2funable-to-alter-table-inside-case-when-within-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Try:
DELIMITER //
-- DROP PROCEDURE IF EXISTS finance.spProcessIndex;
DROP PROCEDURE IF EXISTS finance.spProcessIndex//
.
.
.
CASE
WHEN actDesc='CREATE' THEN
.
.
.
CASE
WHEN tblName='tbl_transactions' THEN
.
.
.
END CASE;
END CASE;
.
.
.
DELIMITER ;
answered Jan 3 at 9:58
wchiquitowchiquito
12.1k22335
Ahhh, I didn't even consider it should be END CASE, as I've used just END in other SPs and it's been fine. Is there a set scenario when you should(n't) use END (CASE)? I've checked the documentation at dev.mysql.com/doc/refman/5.7/en/case.html but still unsure.
– aSystemOverload
Jan 3 at 10:08
@aSystemOverload: CASE expression, differs from the CASE statement, it is terminated withENDinstead ofEND CASE.
– wchiquito
Jan 3 at 10:13
add a comment |
1
Try:
DELIMITER //
-- DROP PROCEDURE IF EXISTS finance.spProcessIndex;
DROP PROCEDURE IF EXISTS finance.spProcessIndex//
.
.
.
CASE
WHEN actDesc='CREATE' THEN
.
.
.
CASE
WHEN tblName='tbl_transactions' THEN
.
.
.
END CASE;
END CASE;
.
.
.
DELIMITER ;
answered Jan 3 at 9:58
wchiquitowchiquito
12.1k22335
Ahhh, I didn't even consider it should be END CASE, as I've used just END in other SPs and it's been fine. Is there a set scenario when you should(n't) use END (CASE)? I've checked the documentation at dev.mysql.com/doc/refman/5.7/en/case.html but still unsure.
– aSystemOverload
Jan 3 at 10:08
@aSystemOverload: CASE expression, differs from the CASE statement, it is terminated withENDinstead ofEND CASE.
– wchiquito
Jan 3 at 10:13
add a comment |
1
1
1
Try:
DELIMITER //
-- DROP PROCEDURE IF EXISTS finance.spProcessIndex;
DROP PROCEDURE IF EXISTS finance.spProcessIndex//
.
.
.
CASE
WHEN actDesc='CREATE' THEN
.
.
.
CASE
WHEN tblName='tbl_transactions' THEN
.
.
.
END CASE;
END CASE;
.
.
.
DELIMITER ;
answered Jan 3 at 9:58
wchiquitowchiquito
12.1k22335
Try:
DELIMITER //
-- DROP PROCEDURE IF EXISTS finance.spProcessIndex;
DROP PROCEDURE IF EXISTS finance.spProcessIndex//
.
.
.
CASE
WHEN actDesc='CREATE' THEN
.
.
.
CASE
WHEN tblName='tbl_transactions' THEN
.
.
.
END CASE;
END CASE;
.
.
.
DELIMITER ;
answered Jan 3 at 9:58
wchiquitowchiquito
12.1k22335
answered Jan 3 at 9:58
wchiquitowchiquito
12.1k22335
answered Jan 3 at 9:58
wchiquitowchiquito
12.1k22335
answered Jan 3 at 9:58
wchiquitowchiquito
12.1k22335
12.1k22335
Ahhh, I didn't even consider it should be END CASE, as I've used just END in other SPs and it's been fine. Is there a set scenario when you should(n't) use END (CASE)? I've checked the documentation at dev.mysql.com/doc/refman/5.7/en/case.html but still unsure.
– aSystemOverload
Jan 3 at 10:08
@aSystemOverload: CASE expression, differs from the CASE statement, it is terminated withENDinstead ofEND CASE.
– wchiquito
Jan 3 at 10:13
add a comment |
Ahhh, I didn't even consider it should be END CASE, as I've used just END in other SPs and it's been fine. Is there a set scenario when you should(n't) use END (CASE)? I've checked the documentation at dev.mysql.com/doc/refman/5.7/en/case.html but still unsure.
– aSystemOverload
Jan 3 at 10:08
@aSystemOverload: CASE expression, differs from the CASE statement, it is terminated withENDinstead ofEND CASE.
– wchiquito
Jan 3 at 10:13
Ahhh, I didn't even consider it should be END CASE, as I've used just END in other SPs and it's been fine. Is there a set scenario when you should(n't) use END (CASE)? I've checked the documentation at dev.mysql.com/doc/refman/5.7/en/case.html but still unsure.
– aSystemOverload
Jan 3 at 10:08
Ahhh, I didn't even consider it should be END CASE, as I've used just END in other SPs and it's been fine. Is there a set scenario when you should(n't) use END (CASE)? I've checked the documentation at dev.mysql.com/doc/refman/5.7/en/case.html but still unsure.
– aSystemOverload
Jan 3 at 10:08
@aSystemOverload: CASE expression, differs from the CASE statement, it is terminated with
END instead of END CASE.– wchiquito
Jan 3 at 10:13
@aSystemOverload: CASE expression, differs from the CASE statement, it is terminated with
END instead of END CASE.– wchiquito
Jan 3 at 10:13
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54019720%2funable-to-alter-table-inside-case-when-within-stored-procedure%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
