[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Using parameters with IN() operator

I have a MySQL 5 stored procedure that takes a TEXT parameter with a comma seperated list of IDs. In the procedure I am wanting to use this to only return the rows with matching IDs using the IN() operator, but am only getting 1 row in the results (from the first ID).

CREATE PROCEDURE `InvoiceSubscriberInfo`(IN _Selected TEXT)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  SELECT *
  FROM tbl_subscriber
  WHERE
         SubscriberID IN (_Selected)
  ORDER BY Company, Branch;
END;

For example if _Selected = 1056,1045,1032 it only returns results for 1056.

0
DerekWatling
Asked:
DerekWatling
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CREATE PROCEDURE `InvoiceSubscriberInfo`(IN _Selected TEXT)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  SELECT *
  FROM tbl_subscriber
  WHERE
         FIND_IN_SET(_Selected,SubscriberID)>0
  ORDER BY Company, Branch;
END;
0
 
DerekWatlingAuthor Commented:
Thanks. As you gave it only works with a single ID otherwise nothing. Swap the parameters around and it works:
FIND_IN_SET(SubscriberID,_Selected)>0
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now