Question 20522 in Mysql Question posted in
The official MySQL documentation can be found here.

Use IN() parameter in stored procedure to pass multiple values in it – MySql database used

For eg :-

Select * from test table where some_words IN("some1","some2")

So in this query how can I write this in stored procedure where the values passed in IN() parameter is user passed values.

In stored procedure-

Create definer Test(In user_input)
Begin
Select* from table where some_words IN(user_input);
End

Here the user_input will have only one value and I want it could accept comma separated multiple values how can I achieve this?

Tags:

3

Answers


  1. 0

    If you are using SQL Server, we can play with the adventure database. Inline table value functions are really important for optimization.

    https://www.sqlshack.com/sql-server-inline-table-valued-function/

    As for this, we can just get away with dynamic T-SQL if the list of input values is small.

    -- simple select
select * from dbo.FactInternetSales
where ProductKey in (336, 346, 311)

    This returns a result set of 350 rows.

    -- multi-line stored procedure
create procedure test2 @list VARCHAR(512)
as
declare @stmt nvarchar(1024);
set @stmt = 'select * from dbo.FactInternetSales where ProductKey in (' + @list + ')';
execute sp_executesql @stmt;

    This stored procedure takes a comma separated list for the in clause as a parameter. This can be enhanced by check if the @list is empty and changing the dynamic T-SQL. I will let you do that.

    A sample call to the stored procedure is below.

    -- get rows via sp call
exec test2 @list = N'336, 346, 311'

    The output is the same for both calls (select vs exec).

    enter image description here

    Login or Signup to reply.
  2. 0

    You can use prepared statements as follows :

    This stored procedure takes comma separated list as parameter.

    CREATE PROCEDURE Test(IN user_input varchar(30))
BEGIN

  SET @sql = CONCAT('select * from mytable where some_words IN ( ', user_input, ' )');
  PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
end

    To call your SP use :

    CALL Test('"some1","some2"');

    Demo here

    Login or Signup to reply.
  3. 0

    The problem with prepared statements is that they lead to SQL Injection. This code has for parts: create table, insert data, check for valid list and return data given a list.

    --
--  1 - create sample table
--

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255)
);


--
--  2 - load sample data
--

-- https://en.wikipedia.org/wiki/List_of_residences_of_presidents_of_the_United_States

-- Insert sample data - President 1
Insert Into Persons values(1, 'Washington', 'George', 'Mount Vernon, Mount Vernon, Virginia');

-- Insert sample data - President 2
Insert Into Persons values(2, 'Adams', 'John', 'Peacefield, Quincy, Massachusetts');

-- Insert sample data - President 3
Insert Into Persons values(3, 'Jefferson', 'Thomas', 'Monticello, Charlottesville, Virginia');


--
--  3 - stored procedure to return presidents by id list
--

DELIMITER $$
CREATE PROCEDURE sp_Valid_List(IN Ids varchar(128), OUT Valid INT)
BEGIN

    -- Declare Variables
    DECLARE Acode INT DEFAULT 0;    
    DECLARE Cnt INT DEFAULT 0;
    
    -- Set Variables
    SET Cnt = 1;
    SET Valid = 1;

   -- Following chars are valid (0-9 and ,)
    WHILE Cnt < LENGTH(Ids)
    DO   
        SET Acode = ASCII(SUBSTRING(Ids, Cnt, 1));
        IF NOT( (Acode >= 48 AND Acode <= 57) OR (Acode = 44) ) THEN
            SET Valid = 0;
        END IF;
        SET Cnt = Cnt + 1;   
    END WHILE;
    
END$$


--
--  4 - stored procedure to return presidents by id list
--

DELIMITER $$
CREATE PROCEDURE sp_GetPresidentById (IN Ids varchar(128))
BEGIN

    -- Variable
    DECLARE RetVal INT DEFAULT 0;
    
    -- Valid list?
    CALL sp_Valid_List(Ids, RetVal);
    
    -- Return results if truee
    IF (RetVal = 1) THEN
        SELECT P.* 
        FROM Persons as P 
        WHERE FIND_IN_SET(P.PersonID, Ids) > 0;
    ELSE
        SELECT '' FROM dual;
    END IF;
    
END$$
DELIMITER ;

    There are three calls to the stored procedure: sql injection, invalid list and valid list. Two out of the three do not return a result since our validation function tells the program not to execute the correct code. Please note, we are using the FIND_IN_SET() function, not the IN(). The later does not work correctly. Implicit casting is occurring with the PersonId.

    -- call sp (SQL Injection)
CALL sp_GetPresidentById('1); DROP TABLE');

-- call sp (bad data)
CALL sp_GetPresidentById ('1 x 2');

-- call sp (good data)
CALL sp_GetPresidentById ('2,3');

    Sample output from the last query.

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search