Stored Procedure Null Error

Question

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

select firstName, lastName, gender, email, userType from user where 1=1 and firstName like '%Test%' ;

User Table

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;

When you call the procedure, do you have backticks around the call as shown in your question? — Sloan Thrasher, Mar 02 '18 at 19:49

score 0 · Answer 1 · answered Mar 02 '18 at 19:55

In MySQL, queryString and @queryString are two different variables.

Variables with the @ prefix are user-defined session variables. Their scope is the MySQL session. That is, when you set this kind of variable inside a procedure, it still has the value you set after the procedure ends, and you can read it as long as your session lasts (or use it in subsequent procedure calls).
Variables with no @ prefix are local variables you create inside a stored procedure or trigger with DECLARE. Their scope is just inside the procedure where they are declared.

You are concatenating into queryString but you are using @queryString as the statement to prepare.

To prove this, I added this line into the procedure just before the prepare:

select queryString, @queryString;

And I get this output:

mysql> call getUserData('Test','','','','',0,0,null,null)\G
*************************** 1. row ***************************
 queryString: select firstName, lastName, gender, email, userType from user where 1=1 and firstName like '%Test%' ;
@queryString: NULL

You can see that @queryString never got any string assigned to it. So when you use it in prepare @queryString it has nothing to prepare.

For some reason MySQL's prepare statement cannot take as its input a local variable you made with DECLARE inside a procedure body. It can only take a user-defined session variable (the kind prefixed with @).

This is a weird oversight in MySQL, and it has been reported to their bugs database at least twice, many years ago. But so far, no action has been taken:

I don't like to use MySQL's stored procedures. They implemented them around 2003 for MySQL 5.0, and they don't seem to have improved the feature significantly since then. There are no packages, no debugger or developer tools, no extensibility, and no persistent compiled state for procedures (they are recompiled on first use in every user connection). Also the documentation is frankly pretty poor.

if I am using prepare stmt from queryString. Its showing me error that **unexpected queryString (identifier)**. Can I use queryString variable with prepare here ? — Aks 1316, Mar 02 '18 at 19:59
As I said above, you can only use a `@`-prefixed session variable with prepare. — Bill Karwin, Mar 02 '18 at 20:00
Just don't use stored procedures in MySQL. Don't bother learning stored procedures. Just learn to use MySQL from a coding language like Python or Ruby or Java or Go. You'll be much happier. — Bill Karwin, Mar 02 '18 at 20:01

score 0 · Answer 2 · answered Mar 02 '18 at 20:23

0

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;

I have referred this link. Prepared Statement in MySql

answered Mar 02 '18 at 20:23

Aks 1316

372
5
19

Stored Procedure Null Error

2 Answers2

Linked