RE: Qyery help - pass string to stored procedure for IN clause - FIXED!

[Price, Randall]

Thanks so much for your help.  With a little tweaking, I finally got
what this to work.  I am using MySQL 5.0.15.  With your query, I get an
error due to the CONCAT:


    CREATE PROCEDURE spGetNames (IN strNames VARCHAR(255))
    BEGIN
        prepare stmt1 from concat('SELECT FirstName,LastName FROM
myTable
WHERE LastName IN (',strNames,')');
        execute stmt1;
    END

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'CONCAT('SELECT FirstName,LastName FROM myTable WHERE
LastName IN (', strNames, ')')' at line 3


So here is what I did to get this working:

    CREATE PROCEDURE spGetNames (IN strNames VARCHAR(255))
    BEGIN
        SET @strSQL = CONCAT('SELECT FirstName,LastName FROM myTable
WHERE LastName IN (', strNames, ')');

          PREPARE stmt1 FROM @strSQL;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END

I did not look up what error 1064 (42000) is but it works now so I will
check into that later.  Also, the manual states to use DEALLOCATE
PREPARE after you are done so I added that as well.  

I also changed the 2 apostrophes to a single apostrophe in the call as
follows:

    CALL spGetNames (" ''PRICE'', ''SMITH'' ");

Changed to:

    CALL spGetNames (" 'PRICE', 'SMITH' ");


Anyway, I got it working so thanks very much.

Randall Price
VT.SETI.IAD.MIG:Microsoft Implementation Group
http://vtmig.vt.edu
[EMAIL PROTECTED]
(540) 231-4396

-----Original Message-----
From: Ing. Edwin Cruz [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 02, 2006 3:03 PM
To: Price, Randall
Subject: RE: Qyery help - pass string to stored procedure for IN clause

What abaut this:

    CREATE PROCEDURE spGetNames (IN strNames VARCHAR(255))
    BEGIN
        prepare stmt1 from concat('SELECT FirstName,LastName FROM
myTable
WHERE LastName IN (',strNames,')');
     execute stmt1;
    END

mysql>CALL spGetNames (" ''PRICE'', ''SMITH'' ");


Try it....
Regards!




-----Mensaje original-----
De: Price, Randall [mailto:[EMAIL PROTECTED] 
Enviado el: Jueves, 02 de Marzo de 2006 12:48 p.m.
Para: mysql@lists.mysql.com
Asunto: Qyery help - pass string to stored procedure for IN clause


I have a SELECT query that looks similar to the following:

    SELECT
        FirstName,
        LastName
    FROM
        myTable
    WHERE
        LastName IN ('PRICE', 'SMITH');

What I want to do is create a stored procedure for this SELECT query
similar
to the following:

    CREATE PROCEDURE spGetNames (IN strNames VARCHAR(255))
    BEGIN
        SELECT
            FirstName,
            LastName
        FROM
            myTable
        WHERE
            LastName IN (strNames);
    END

And then I would like to call it by passing in a list of names.  None of
the
following calls work:

        CALL spGetNames ('PRICE,SMITH');
        CALL spGetNames (" 'PRICE', 'SMITH' ");
        CALL spGetNames (" ''PRICE'', ''SMITH'' ");

My question is how to format the string parameter so this query works.

Thanks,

Randall Price
VT.SETI.IAD.MIG:Microsoft Implementation Group http://vtmig.vt.edu
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]