Mysql 5 Using Multiple In Statements In Query/stored Proc
April 15, 2008 - 8:25pm
Someone on MySQL Forums
Someone on MySQL Forums posted a solution that seems to work:
CODE
DECLARE vSize VARCHAR(50);
DECLARE vQty VARCHAR(50);
DECLARE vValue VARCHAR(50);
DECLARE vQty VARCHAR(50);
DECLARE vValue VARCHAR(50);
SET @vSql = "SELECT [fieldname] FROM [tablename] WHERE ";
IF(Quantity IS NULL)THEN
SET @vSql := CONCAT(@vSql," Size IN ('", vSize , "') AND Value IN ('", vValue ,"')");
ELSEIF(Size IS NULL)THEN
SET @vSql := CONCAT(@vSql," Quantity IN ('", vQty , "') AND Value IN ('", vValue ,"')");
ELSE
(Till your condition met)
END IF;
PREPARE vStmt FROM @vSql;
EXECUTE vStmt;
April 16, 2008 - 12:20pm
The IN( operator should not
The IN() operator should not have quotes around the full set, but (in the case of strings) quotes around each item. So:
CODE
IN('a','b','c');
IN(1,2,3,6);
IN(1,2,3,6);
On the other hand, FIND_IN_SET(str,strlist) does behave like this, with the set quoted as one string.
I've been out of practice with MySQL for about 2 years now, and MySQL 5 introduces Stored Procedures, which I've been using quite a bit with my day job in Microsoft SQL Server 2005/2008.
The Problem:
I cannot for the life of me get MySQL5 to accept a SQL statement that takes the following form:
DECLARE strQuantity VARCHAR(50);
DECLARE strValue VARCHAR(50);
SET strSize = '1,2,3,4,5';
SET strQuantity = '1,2,3,4,5';
SET strValue = '1,2,3,4,5';
SELECT [fieldname] FROM [tablename]
WHERE Size IN (strSize)
AND Quantity IN (strQuantity)
AND Value IN (strValue)
I CAN get MySQL5 to accept a statement that has these values hard-coded into the IN() operator, but that is really of no use, as I'd like to programmatically change the value of my local sp variables [strSize, strQuantity, strValue] on the fly to help filter down all of the records that might be in [tablename] This sample works, but doesn't allow me to filter down the available items for the IN() operator.
WHERE Size IN ('1,2,3,4,5')
AND Quantity IN ('1,2,3,4,5')
AND Value IN ('1,2,3,4,5')
What is your desired result?
I want to write a single stored procedure that can accept multiple INPUT parameters/variables. If these variables are passed into the stored procedure, then we use just that specific value. If there is no value set to the INPUT parameter/variable, then I want the query to look at ALL records, not filtering on this specific column from the DB.
Examples:
WHERE Size IN ('1')
AND Quantity IN ('1,2,3,4,5')
AND Value IN ('3')
WHERE Size IN ('1,2,3,4,5')
AND Quantity IN ('1,2,3,4,5')
AND Value IN ('3')
RubberPhotos - PHP/MySQL Custom Photo Album Software
Christian T-shirts, Clothing, and Apparel