Now that I think about it, if MySql forced declaration of session variables it would avoid some nasty bugs in SPs.
>>> Mark Matthews 08/09/05 08:46AM >>> -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Kent Roberts wrote: > I have a simple SP that is selecting rows based on a date range using parameters. I've tried several permutations that all return 0 rows. > > The select statement looks like this: > > select * from 'mit_log'.'mitlog' where StartDateTime between '2005-04-01' and '2005-04-07' > > and returns over 300,000 rows. The SP looks like this: > > ------------------------------------------------------------------------ ----------------------------------------- > DELIMITER $$ > > DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$ > > Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) > BEGIN > SELECT * > FROM mitlog > WHERE (mitlog.StartDateTime between @begDate And @endDate); Kent, If you prefix things with @, they are session variables. You want something like the following, I believe: Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between begDate And endDate); -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+MHjtvXNTca6JD8RAvcSAKCayRHpQZBCbxkfKYmQb+f5RmFsXQCfec/l AupP4lU40BKSNF49w9DJto0= =SJIl -----END PGP SIGNATURE-----
