存储过程返回错误值 [英] 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;

当我运行这个存储过程时，它返回所有行并显示 profieId 作为传入的 profileId ，这是我的存储过程

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);

请看截图

查询截图

这是存储过程的错误结果

this is the incorrect result from stored procedure

你可以看到，mysql 说所有图像都属于我传入的 profileId 5 .我的存储过程有什么问题

you can see, mysql says all the images belongs to profileId 5 , which i passed in. What is wrong with my stored procedure

推荐答案

您的问题是您的输入参数与表中的字段同名，而在您的查询中 MySQL 将 profileId 解释为改为字段名称.因此，您的 where profileId = profileId 始终为真，您将获得所有行.更改输入参数的名称，例如

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

这篇关于存储过程返回错误值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！