存储过程空错误 [英] Stored Procedure Null Error

问题描述

我正在学习 Mysql 存储过程，但出现错误错误代码:1064.您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的NULL"附近使用的正确语法

I am learning Mysql stored procedure and I am getting error Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

delimiter //

drop procedure if exists getUserData//
create procedure getUserData(
in firstName varchar(50),
in lastName varchar(50),
in gender varchar(50),
in email varchar(50),
in userType varchar(50),
in isActive int,
in isDeleted int,
in createdBy date,
in dob date
)
begin

declare queryString text default "";

set queryString = concat(queryString, 'select firstName, lastName, gender, email, userType from user where 1=1');

if (firstName is not null and firstName != '') then
    set queryString = concat(queryString," and firstName like '%", firstName,"%' ");
end if;

if (lastName is not null and lastName != '') then
    set queryString = concat(queryString," and lastName like '%", lastName,"%' ");
end if;

if (gender is not null and gender != '') then
    set queryString = concat(queryString," and gender = '", gender,"' ");
end if;

if (email is not null and email != '') then
    set queryString = concat(queryString," and email like '%", email,"%' ");
end if;

if (userType is not null and userType != '') then
    set queryString = concat(queryString," and userType = '", userType,"' ");
end if;

if (isActive is not null and isActive != '') then
    set queryString = concat(queryString," and isActive = ", isActive);
end if;

if (isDeleted is not null and isDeleted != '') then
    set queryString = concat(queryString," and isDeleted = ", isDeleted);
end if;

if (createdBy is not null and createdBy != '') then
    set queryString = concat(queryString," and createdBy = ", createdBy);
end if;

if (dob is not null and dob != '') then
    set queryString = concat(queryString," and dob = ", dob);
end if;

set queryString = concat(queryString, ";");

-- select queryString;

prepare stmt from @queryString;
execute stmt;
deallocate prepare stmt;

end//

delimiter ;

我已经打印了结果查询，当我单独运行该查询时它工作正常，但在运行存储过程时显示错误.我无法理解什么是实际错误以及我在哪里犯了错误.调用过程是，结果查询是

I have printed my resulting query which is working fine when I am running that query separately but while running stored procedure its showing error. I am not able to understand what is actual error and where I am making mistake. Call to procedure is and resulting query is

call getUserData('Test','','','','',0,0,null,null);

从用户中选择名字、姓氏、性别、电子邮件、用户类型，其中 1=1 和名字如 '%Test%' ;

用户表

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
createdBy varchar(255) DEFAULT NULL,
createdOn datetime DEFAULT NULL,
updatedBy varchar(255) DEFAULT NULL,
updatedOn datetime DEFAULT NULL,
dob datetime DEFAULT NULL,
email varchar(255) DEFAULT NULL,
firstName varchar(255) DEFAULT NULL,
gender varchar(255) DEFAULT NULL,
isActive tinyint(4) DEFAULT '5',
isDeleted tinyint(4) DEFAULT '0',
lastName varchar(255) DEFAULT NULL,
userId varchar(255) DEFAULT NULL,
userType varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

推荐答案

添加此行解决了我的问题.我已经创建了 @SQL 变量并将我的 queryString 分配给它，现在它工作正常.

Adding this lines resolved my issue. I have created @SQL variable and assigned my queryString to it and now its working fine.

SET @SQL := queryString;
prepare stmt from @SQL;

我已经提到了这个链接.MySql 中准备好的语句

I have referred this link. Prepared Statement in MySql

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