Error when passing multiple values

Jassim Rahma

Hi, I have below stored procedure.. it only works when I pass single value like "US" but not when passing multiple values like "AE", "BH", "US", "FR" when passing multiple values I get this error:

Procedure execution failed
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 '")' at line 1

here is the stored procedure:

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_populate_memo_country_companies`(IN param_country varchar(255))
BEGIN
SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
FROM locations
JOIN country ON country.country_code_alpha2 = locations.country_code
JOIN companies ON companies.company_id = locations.company_id
LEFT JOIN payroll ON payroll.location_id = locations.location_id
WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
GROUP BY locations.location_id
ORDER BY companies.company_name;
END

Kindly help... Thanks, Jassim[^]

Technology News @ www.JassimRahma.com

Mycroft Holmes

Same answer as last week! You need to split the string into individual values.

Never underestimate the power of human stupidity RAH

Jassim Rahma

it is indiviual values. For example, If I try:

WHERE IN("BH", "US", "FR")

it will work but when I try:

IN(param_country)

then pass "BH", "US", "FR" it won't work.

Technology News @ www.JassimRahma.com

Lost User

What is the exact content of param_country when you try to execute this procedure? The error message is clearly telling you that some part of it is not valid syntax for this version of MySQL.

Jassim Rahma

It's northing to do with param_country because I am getting the problem when I run the stored procedure on MySQL Server console as well (without any C#) For example, if I run:

CALL sp_populate_memo_country_companies("BH")

it works fine but when I run:

CALL sp_populate_memo_country_companies("BH", "US")

I get:

[SQL] CALL sp_populate_memo_country_companies("BH", "US")

[Err] 1318 - Incorrect number of arguments for PROCEDURE bluefile.sp_populate_memo_country_companies; expected 1, got 2

Technology News @ www.JassimRahma.com

Lost User

Jassim Rahma wrote:

Incorrect number of arguments

What part of that message do you find difficult to understand?

Jassim Rahma

this part:

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_populate_memo_country_companies`(IN param_country varchar(255))

there is only argument which is param_country which I am passing but maybe in the wrong way.. I mean in my above case, how can I pass an IN parameter for:

WHERE country_code IN (param_country)

Technology News @ www.JassimRahma.com

Victor Nijegorodov

Jassim Rahma wrote:

but when I run:

CALL sp_populate_memo_country_companies("BH", "US")

I get:

[SQL] CALL sp_populate_memo_country_companies("BH", "US")

[Err] 1318 - Incorrect number of arguments for PROCEDURE bluefile.sp_populate_memo_country_companies; expected 1, got 2

Just pack all the patterns for IN clause in one varchar/string value and pass this value as a single argument!

Jassim Rahma

I tried:

CALL sp_populate_memo_country_companies("BH, US")

but returns no rows when BH alone returns 5 rows.

Technology News @ www.JassimRahma.com

Mycroft Holmes

:sigh: Does this not tell you something, SQL treats the parameter as a single value and you get an error! You need to split the string into individual values. You need a split function for MySQL below is the SQL Server version I use

ALTER FUNCTION [dbo].[fn_Split]
(@List varchar(8000), @Delimiter char(1))

RETURNS @Results table
(Item varchar(8000),ID int Identity(1,1))

AS

begin
declare @IndexStart int
declare @IndexEnd int
declare @Length int
declare @Word varchar(8000)

set @IndexStart = 1
set @IndexEnd = 0

set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','

--Get rid of any tabs or returns
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')

while @IndexStart <= @Length
begin
set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)

If @Delimiter = char(32) 
	set @IndexEnd = charindex(Space(1), @List, @IndexStart)

if @IndexEnd = 0
	set @IndexEnd = @Length + 1

set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
set @IndexStart = @IndexEnd + 1

INSERT INTO @Results(Item)
SELECT @Word

end

return
end

Then you can use a join to filter the location table

DECLARE @StringParam VARCHAR(100)= 'AA,BB,CC'

--Test
SELECT * FROM dbo.fn_split(@StringParam,',')

SELECT * FROM Location L
INNER JOIN dbo.fn_split(@StringParam,',') S ON S.item = l.country_code

This is SQL Server code, you may need to change it for MySql

Never underestimate the power of human stupidity RAH

Jassim Rahma

how can I convert this to MySQL's PL/SQL?

Technology News @ www.JassimRahma.com

Mycroft Holmes

That is your job, I don't run MySQL. You need to get to know PL SQL or search the support sites for specific problems you run into. There is nothing in there that is specific to TSQL.

Never underestimate the power of human stupidity RAH