Click here to Skip to main content
14,734,150 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to filter the records with where clause in procedure based on IN input parameter values I had written the stored procedure in My SQL as like below :

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_logs_Test`(IN `p_Bot_Ids` VARCHAR(500))
    NO SQL
select id, bot_id, log_level,log_type,time_stamp,finger_print,windows_identity,machine_name,process_name,process_version
,job_Id,robot_name,machine_Id,file_name,message,created_date from log
where id < 200 and
bot_id IN (p_Bot_Ids)$$
DELIMITER ;


What I have tried:

My "p_Bot_Ids" is a comma separated string. If I pass "1,2" its filtering only with "1". if I pass "2,1" its filtering with only "2".

Also my "p_Bot_Ids" can also be null.In that case I needs to pull out all the records.

Let us consider there are 50 records in my table. If i pass "p_Bot_Ids" = "1,2,3" I needs to get only 1,2,3 Ids records(total 3 rows) only. If I pass "p_Bot_Ids" = NULL then I should get all my 50 records.
Posted
Updated 14-Apr-20 18:09pm
Comments
PIEBALDconsult 14-Apr-20 21:41pm
   
I'm unfamiliar with MySQL, but how about something like this, which might work with SQL Server:
WHERE CHARINDEX ( ',' + bot_id + ',' , ',' + p_Bot_Ids + ',' ) > 0

1 solution

The requirements are a bit different in different parts or your post but if the following is correct
Tech Box To Unbox wrote:
Let us consider there are 50 records in my table. If i pass "p_Bot_Ids" = "1,2,3" I needs to get only 1,2,3 Ids records(total 3 rows) only. If I pass "p_Bot_Ids" = NULL then I should get all my 50 records.

You could try something like
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_logs_Test`(IN `p_Bot_Ids` VARCHAR(500))
    NO SQL

SET @sql := 'select id, bot_id, log_level,log_type,time_stamp,finger_print,windows_identity,machine_name,process_name,process_version
,job_Id,robot_name,machine_Id,file_name,message,created_date from log
where id < 200';

IF p_Bot_Ids IS NOT NULL THEN
   SET @sql := CONCAT(@sql, ' AND bot_id IN (', p_Bot_Ids, ')');
END IF ;

PREPARE mystatement FROM @sql;
EXECUTE mystatement;
DEALLOCATE PREPARE mystatement$$
DELIMITER ;

For more information, have a look at MySQL :: MySQL 8.0 Reference Manual :: 13.5.1 PREPARE Statement[^]
   
v4
Comments
Wendelius 14-Apr-20 23:14pm
   
Null check added
Tech Box To Unbox 16-Apr-20 1:15am
   
What happens if "p_Bot_Ids" is NULL in the CONCAT ? Will it fetch all the records ?
Wendelius 16-Apr-20 10:48am
   
Because the concatenation is inside an if statement, if p_Bot_Ids is null then the concatenation will not happen.

To answer if the query will fetch all rows, it will fetch the rows having id less than 200 because that is a constant condition.
Maciej Los 15-Apr-20 1:36am
   
5ed!
Wendelius 16-Apr-20 10:52am
   
Thanks!

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900