CODEWITHSUNDEEP
mysqlstored-procedures
root
root

Reputation: 151

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

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.

Upvotes: 0

Views: 51

Answers (3)

root
root

Reputation: 151

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.

Upvotes: 0

TonyWaddle
TonyWaddle

Reputation: 103

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/

Upvotes: 0

Rahul
Rahul

Reputation: 77896

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;

Upvotes: 1

Related Questions