0

I have a procedure A which takes an array of strings. By calling another procedure B, i break this array in this format:

 '1','2','3','4'

In case there is only one value, it just displays as '1'

I want to return * in case the array passed to the procedure A. Therefore, my query will be like this: select * from users where userId(*); What i want is that in case the parameter is null, it should still perform the IN using.

** EDIT ** Nothing much of query

CREATE DEFINER=`root`@`localhost` PROCEDURE `listAll`(IN id varchar(200))
BEGIN
    set @t = lib_explode(',',id);

     select * from city where ID in(@t);
END

Procedure B

CREATE DEFINER=`root`@`localhost` FUNCTION `lib_explode`(sSepar VARCHAR(255), saVal TEXT) RETURNS varchar(200) CHARSET utf8
body:
BEGIN

IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

  SET @saTail = saVal;
  SET @iSeparLen = LENGTH( sSepar );
  set @mystring = '';
set @current_pos = 1;
create_layers:
  WHILE @saTail != '' DO

    # Get the next value
    SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
   -- INSERT INTO lib_Explode SET val = @sHead;
    if(@current_pos > 1) then
   set @mystring = concat(@mystring,',',concat("'",@shead,"'"));
    else 
    set @mystring = concat(@mystring,concat("'",@shead,"'"));
    end if;
    set @current_pos = @current_pos + 1;

  END WHILE;
return @mystring;
END
sameer
  • 220
  • 2
  • 6
  • 18
  • 3
    Please post your whole query. This can be solved with a simple `WHERE` condition comparing your input parameter to `NULL`. (`WHERE column IN (:param) OR :param IS NULL`) – Michael Berkowski Oct 05 '13 at 12:58
  • Wait, sorry I didn't see this sooner - your `lib_explode()` function returns a `VARCHAR()` type. You cannot insert that into the `IN()` clause as `@t` because MySQL will interpret it as a single string, not a comma-separated list of values. What is your input for `saVal`? – Michael Berkowski Oct 05 '13 at 14:46
  • it is just a list of values comma separated.. for example, 1,2,3.. then i just do call listAll('1,2,3'); – sameer Oct 05 '13 at 14:52
  • @MichaelBerkowski did you delete the answer you put below? – sameer Oct 05 '13 at 16:17
  • Yes, because it can't help you. Your problem is that you are attempting to pass the string value `"'1','2','3','4','5'"` (note the outer quotes) into an `IN ()` clause rather than passing the discrete values `'1','2','3','4','5'` into it. I don't have a good solution. MySQL receives that string and attempts to cast it to an integer. As a non-numeric string it casts to integer 0 and you get no rows matched. – Michael Berkowski Oct 05 '13 at 16:40
  • A possible solution, but not a good solution, is to construct the SQL string using the values in the `IN()` clause as you have as a complete string value, then pass it to `EXEC()` in another stored procedure. You have then turned to dynamic SQL, and it can potentially be a security risk. – Michael Berkowski Oct 05 '13 at 16:45
  • @MichaelBerkowski is the usage of temporary table a good practice and what effect it has on execution time? – sameer Oct 06 '13 at 04:12

0 Answers0