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]