IN() not working in mysql stored procedure

Question

4.00/5 (1 vote)

See more:

SQL

DELIMITER $$

USE `employee`$$

DROP PROCEDURE IF EXISTS `selectemployeebyids`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `selectemployeebyids`(IN empid VARCHAR(100))
BEGIN
    SELECT empname,salary,department from emp where empid IN (empid) ORDER BY empid;
    END$$

DELIMITER ;

Can anyone please help me

Manju K

Posted 22-Jul-12 22:06pm

manjukgowda

Add a Solution

Comments

AmitGajjar 23-Jul-12 4:30am

Whats your problem ? Please post error message.

manjukgowda 23-Jul-12 5:19am

Actually by the above procedure i want to retrive empname , salary , department from employee table whose employee id is 101,102,103 and 104

AmitGajjar 23-Jul-12 5:23am

if you are passing empid as 101,102,103.. then your query should return result. execute this query individually and check result.

manjukgowda 23-Jul-12 5:28am

I'm passing that empids as varchar so that it takes as

Where empid IN ('101,102,103,104')

it returns result only for empid = 101

AmitGajjar 23-Jul-12 5:29am

do you have other records in database ? are you executing this query individually or by using exec ?

manjukgowda 23-Jul-12 5:33am

I called that procedure from asp.net application

MySqlCommand cmd = new MySqlCommand("selectemployeebyids", Common.ConnectionStringManager.GetConnectionString());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
cmd.Parameters.AddWithValue("empid", id);
MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adap.Fill(dt);
cmd.Connection.Close();

return dt;

AmitGajjar 23-Jul-12 5:38am

it's look perfect from your code. Debug your application and check if your datatable dt filled with all data ? if no then check your stored procedure.

manjukgowda 23-Jul-12 5:39am

http://www.codeproject.com/Articles/6083/Passing-comma-delimited-parameter-to-stored-proced#_rating

Here i got a solution that works in sqlserver superbly but i'm unable to do it mysql

Use Northwind

Go
-- @IDs is the string of comma delimited integers
-- (you can increase the size if you think that your array will be very large)
-- After parsings the string, integers are returned in a table

CREATE Function fnSplitter (@IDs Varchar(100) )
Returns @Tbl_IDs Table (ID Int) As

Begin
-- Append comma
Set @IDs = @IDs + ','
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int

-- Start from first character
Set @Pos1=1
Set @Pos2=1

While @Pos1

AmitGajjar 23-Jul-12 5:53am

we can't access your computer so please provide us some information,
1) is your query is giving you correct result if you are executing individually (not from SP) ?
2) is your SP gives correct result ?
3) is your DataTable gives correct result ?

manjukgowda 23-Jul-12 5:59am

Yes got correct result when i executed the query in query window

My stored procedure also gives correct result when i executed as follows

exec selectemployeebyids(101,102,103,104)

- Gives correct result i.e all employees 101,102,103,104

exec selectemployeebyids('101,102,103,104')

- gives result only for employee id 101

AmitGajjar 23-Jul-12 6:03am

Congratulation...

manjukgowda 23-Jul-12 6:07am

:)

manjukgowda 23-Jul-12 6:08am

Can u please help me to do this, i'm very disappointed from morning

AmitGajjar 23-Jul-12 6:55am

Please see my solution. Mark it as answered if it works for you.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**AmitGajjar** · Answer 1 · 2012-07-23T00:11:00

Solution 1

Hi,

Just Remove Quotation mark from your C# code when you are passing your value in your stored procedure.

That may work for you,

Thanks
-Amit Gajjar.

Posted 23-Jul-12 0:11am

AmitGajjar