MySQL string issue with stored procedures. Raw output looks fine, but returning empty set in query

Question

Having some MySQL trouble.

To be specific, I have written a stored procedure to take in a few inputs, one of which is a comma-delimited list, for a dynamic query.

For example:

SET @var1 = 10;
SET @var2 = 'banana';
SET @var3 = "\'Thing1\', \'Thing2\'";
CALL my_stored_proc(@var1, @var2, @var3);

The stored procedure code itself looks at the passed-in list, in this case @var3, and checks for membership. Looks like this in the procedure:

SELECT item, some_stuff FROM table
WHERE item IN (variable3)
GROUP BY item;

Now, I've looked at @var3 and the raw output seems to be fine. When I copy and paste the text directly in, replacing variable3 in the 'IN(variable3)' statement, the query works as expected!

Tried passing in the double-quote-single-quote-escaped bit (see @var3) to the CALL directly, and still no luck.

Any help would be greatly appreciated.

Show exactly how you are calling the procedure?

Rahul
– Rahul

2016-09-27 22:29:36 +00:00
Commented Sep 27, 2016 at 22:29 — Rahul
– Rahul, Commented Sep 27, 2016 at 22:29

Rahul · Accepted Answer · 2016-09-27 22:33:22Z

1

Show exactly how you are calling the procedure? But anyways, that's a wrong approach. Instead you should use a temporary table -> fill those data -> Use that temp table in your IN clause

CREATE TEMPORARY TABLE dumb (things varchar(20));

insert into dumb
select 'Thing1'
union
select 'Thing2'

Use that temp table in your procedure

SELECT item, some_stuff FROM table
WHERE item IN (SELECT distinct things from dumb)
GROUP BY item;

answered Sep 27, 2016 at 22:33

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TonyWaddle · Accepted Answer · 2016-09-27 22:54:31Z

0

I'm not sure if this is specific to MySQL but that won't work in SQL Server. Alternative ways around it include:

Using a table valued parameter to pass the list of strings in directly.

Pass in a delimited string to split into a table using some string splitting function:

DECLARE @Param1 VARCHAR(50) = 'val1, val2, val3';

SELECT  string
INTO    #Param1
FROM    fnSplitString(@Param1, ',');

You can then use this in your where clause:

WHERE item IN (SELECT string FROM #Param1)

Split string function: https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

edited Sep 27, 2016 at 22:54

answered Sep 27, 2016 at 22:33

TonyWaddle

1031 silver badge5 bronze badges

3 Comments

Rahul Over a year ago

It's specific to MySQL by seeing the syntax for procedure call CALL my_stored_proc(... you don't use CALL in SQL Server

TonyWaddle Over a year ago

I meant trying to use a scalar delimited string within an IN clause - I wasn't sure if that was possible in MySQL, but I assume it isn't?

Rahul Over a year ago

I don't believe there is an built in function fnSplitString() present unless you write one though

root · Accepted Answer · 2016-09-28 15:51:43Z

0

thanks for the help. Solved my issue a little differently than the other methods here.

Ideally, I wanted to pass in a list of strings to the stored procedure. Solved this with using the FIND_IN_SET built-in MySQL method. As opposed to a string with substrings (see original post's @var3), FIND_IN_SET allows you to pass in one string.

For example:

SET @var3 = 'thing1,thing2'
CALL my_stored_proc(@var1, @var2, @var3)

then within the stored procedure:

WHERE FIND_IN_SET(item, variable3)

Thanks for the help, everyone. Hopefully this thread will assist anyone attempting to pass in dynamic information to a stored procedure, without using a temp table or escape characters.

answered Sep 28, 2016 at 15:51

root

1512 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySQL string issue with stored procedures. Raw output looks fine, but returning empty set in query

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related