Stored procedure returns incorrect values
My query returns correct values , but when i run the query as a stores procedure it returns incorrect results. this is my query which returns correct values
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = 5;
and when i run this stored procedure, it is returning all rows and shows profieId as the passed in profileId , this is my stored procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in profileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = profileId;
END
this is how i call the procedure
CALL `tfm`.`getImagesForUser`(5);
Please see the screenshots
screenshot for query
this is the incorrect result from stored procedure
you can see, mysql says all the images belongs to profileId 5 , which i passed in.
What is wrong with my stored procedure
mysql stored-procedures
asked Nov 17 '18 at 20:33
SonySony
3,82632537
add a comment |
My query returns correct values , but when i run the query as a stores procedure it returns incorrect results. this is my query which returns correct values
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = 5;
and when i run this stored procedure, it is returning all rows and shows profieId as the passed in profileId , this is my stored procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in profileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = profileId;
END
this is how i call the procedure
CALL `tfm`.`getImagesForUser`(5);
Please see the screenshots
screenshot for query
this is the incorrect result from stored procedure
you can see, mysql says all the images belongs to profileId 5 , which i passed in.
What is wrong with my stored procedure
mysql stored-procedures
asked Nov 17 '18 at 20:33
SonySony
3,82632537
1
you're creating an SPgetImagesForUserbut you're calling one calledgetAllImagesForUser, is that a typo?
– Reinder Wit
Nov 17 '18 at 20:36
sorry that was a typo,getAllImagesForUserwas the original one and i dropped it and createdgetImagesForUser. I have edited my question
– Sony
Nov 17 '18 at 20:37
add a comment |
My query returns correct values , but when i run the query as a stores procedure it returns incorrect results. this is my query which returns correct values
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = 5;
and when i run this stored procedure, it is returning all rows and shows profieId as the passed in profileId , this is my stored procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in profileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = profileId;
END
this is how i call the procedure
CALL `tfm`.`getImagesForUser`(5);
Please see the screenshots
screenshot for query
this is the incorrect result from stored procedure
you can see, mysql says all the images belongs to profileId 5 , which i passed in.
What is wrong with my stored procedure
mysql stored-procedures
asked Nov 17 '18 at 20:33
SonySony
3,82632537
My query returns correct values , but when i run the query as a stores procedure it returns incorrect results. this is my query which returns correct values
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = 5;
and when i run this stored procedure, it is returning all rows and shows profieId as the passed in profileId , this is my stored procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in profileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = profileId;
END
this is how i call the procedure
CALL `tfm`.`getImagesForUser`(5);
Please see the screenshots
screenshot for query
this is the incorrect result from stored procedure
you can see, mysql says all the images belongs to profileId 5 , which i passed in.
What is wrong with my stored procedure
mysql stored-procedures
mysql stored-procedures
asked Nov 17 '18 at 20:33
SonySony
3,82632537
asked Nov 17 '18 at 20:33
SonySony
3,82632537
edited Nov 17 '18 at 20:37
Sony
asked Nov 17 '18 at 20:33
SonySony
3,82632537
asked Nov 17 '18 at 20:33
SonySony
3,82632537
asked Nov 17 '18 at 20:33
SonySony
3,82632537
3,82632537
1
you're creating an SPgetImagesForUserbut you're calling one calledgetAllImagesForUser, is that a typo?
– Reinder Wit
Nov 17 '18 at 20:36
sorry that was a typo,getAllImagesForUserwas the original one and i dropped it and createdgetImagesForUser. I have edited my question
– Sony
Nov 17 '18 at 20:37
add a comment |
1
you're creating an SPgetImagesForUserbut you're calling one calledgetAllImagesForUser, is that a typo?
– Reinder Wit
Nov 17 '18 at 20:36
sorry that was a typo,getAllImagesForUserwas the original one and i dropped it and createdgetImagesForUser. I have edited my question
– Sony
Nov 17 '18 at 20:37
1
1
you're creating an SP
getImagesForUser but you're calling one called getAllImagesForUser, is that a typo?– Reinder Wit
Nov 17 '18 at 20:36
you're creating an SP
getImagesForUser but you're calling one called getAllImagesForUser, is that a typo?– Reinder Wit
Nov 17 '18 at 20:36
sorry that was a typo,
getAllImagesForUser was the original one and i dropped it and created getImagesForUser. I have edited my question– Sony
Nov 17 '18 at 20:37
sorry that was a typo,
getAllImagesForUser was the original one and i dropped it and created getImagesForUser. I have edited my question– Sony
Nov 17 '18 at 20:37
add a comment |
1 Answer
1
active
oldest
votes
Your problem is that your input parameter has the same name as the field in your table, and inside your query MySQL interprets profileId as being the field name instead. Thus your where profileId = profileId is always true, and you get all rows. Change the name of the input parameter e.g.
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in searchProfileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = searchProfileId;
END
answered Nov 17 '18 at 22:24
NickNick
26.3k111836
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
});
}
});
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%2f53355320%2fstored-procedure-returns-incorrect-values%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
Your problem is that your input parameter has the same name as the field in your table, and inside your query MySQL interprets profileId as being the field name instead. Thus your where profileId = profileId is always true, and you get all rows. Change the name of the input parameter e.g.
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in searchProfileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = searchProfileId;
END
answered Nov 17 '18 at 22:24
NickNick
26.3k111836
add a comment |
Your problem is that your input parameter has the same name as the field in your table, and inside your query MySQL interprets profileId as being the field name instead. Thus your where profileId = profileId is always true, and you get all rows. Change the name of the input parameter e.g.
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in searchProfileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = searchProfileId;
END
answered Nov 17 '18 at 22:24
NickNick
26.3k111836
add a comment |
Your problem is that your input parameter has the same name as the field in your table, and inside your query MySQL interprets profileId as being the field name instead. Thus your where profileId = profileId is always true, and you get all rows. Change the name of the input parameter e.g.
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in searchProfileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = searchProfileId;
END
answered Nov 17 '18 at 22:24
NickNick
26.3k111836
Your problem is that your input parameter has the same name as the field in your table, and inside your query MySQL interprets profileId as being the field name instead. Thus your where profileId = profileId is always true, and you get all rows. Change the name of the input parameter e.g.
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in searchProfileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = searchProfileId;
END
answered Nov 17 '18 at 22:24
NickNick
26.3k111836
answered Nov 17 '18 at 22:24
NickNick
26.3k111836
answered Nov 17 '18 at 22:24
NickNick
26.3k111836
answered Nov 17 '18 at 22:24
NickNick
26.3k111836
26.3k111836
add a comment |
add a comment |
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%2f53355320%2fstored-procedure-returns-incorrect-values%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
1
you're creating an SP
getImagesForUserbut you're calling one calledgetAllImagesForUser, is that a typo?– Reinder Wit
Nov 17 '18 at 20:36
sorry that was a typo,
getAllImagesForUserwas the original one and i dropped it and createdgetImagesForUser. I have edited my question– Sony
Nov 17 '18 at 20:37