Re: Stored procedures MS SQL Server to MySQL
On Sun, Sep 25, 2005 at 03:56:46PM -0500, Peter Brawley wrote: > Jim, > > If you really want to return the result in a variable, declare a user > var in the client, declare an OUT var in the SP, have the SP SELECT INTO > it, and pass the user var to the SP in the call: > > SET @x=0; > SET GLOBAL log_bin_trust_routine_creators = TRUE; > DROP PROCEDURE IF EXISTS CountPhoneNumbers; > DELIMITER | > CREATE PROCEDURE CountPhoneNumbers ( OUT count INT ) > BEGIN >SELECT COUNT(*) INTO count >FROM customer >WHERE Phone IS NOT NULL; > END; > | > DELIMITER ; > CALL CountPhoneNumbers(@x); > SELECT @x; > Thanks Peter, Since she was teaching us about using a variable I am trying your second example. Thanks again, Jim Seymour -- I started using something better than the "standard" back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. "Use the Power of the Penguin" Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures MS SQL Server to MySQL
Jim, >I have tried numerous variations of the following: >CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT >@count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END// >// was set to be the delimiter for the creation and have tried putting >various parts of the query into the parentheses. The error messages >always seem to occur around the @count variable. I am using MySQL >v.5.0.12 on Debian Etch. The stored procedure docs (http://dev.mysql.com/doc/mysql/en/create-procedure.html) might not forbid prefixing declared variable names prefixed with @, but all the examples on that page show vars without such a prefix. The @ prefix in MySQL is for client user vars. The simplest way to retreive a count frm an SP is just to run the query in the SP. MySQL will return the value, for example: SET GLOBAL log_bin_trust_routine_creators = TRUE; DROP PROCEDURE IF EXISTS CountPhoneNumbers; DELIMITER | CREATE PROCEDURE CountPhoneNumbers () BEGIN SELECT COUNT(*) FROM customer WHERE Phone IS NOT NULL; END; | DELIMITER ; CALL CountPhoneNumbers(); +--+ | COUNT(*) | +--+ | 91 | +--+ (Set log_bin_trust_routine_creators to bypass MySQL's declaration rules regarding deterministicness.) If you really want to return the result in a variable, declare a user var in the client, declare an OUT var in the SP, have the SP SELECT INTO it, and pass the user var to the SP in the call: SET @x=0; SET GLOBAL log_bin_trust_routine_creators = TRUE; DROP PROCEDURE IF EXISTS CountPhoneNumbers; DELIMITER | CREATE PROCEDURE CountPhoneNumbers ( OUT count INT ) BEGIN SELECT COUNT(*) INTO count FROM customer WHERE Phone IS NOT NULL; END; | DELIMITER ; CALL CountPhoneNumbers(@x); SELECT @x; PB http://www.artfulsoftware.com - Jim Seymour wrote: Taking on online class for SQL and am down to the last two classes and cannot make the following work. This is a MS SQL Server query that I have not been able to solve through the MySQL Documentation: CREATE PROCEDURE CountPhoneNumbers AS DECLARE @count INTEGER SELECT @count = COUNT (*) FROM Customer WHERE HomePhone IS NOT NULL Print @count I have tried numerous variations of the following: CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT @count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END// // was set to be the delimiter for the creation and have tried putting various parts of the query into the parentheses. The error messages always seem to occur around the @count variable. I am using MySQL v.5.0.12 on Debian Etch. TIA, Jim Seymour -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedures MS SQL Server to MySQL
Taking on online class for SQL and am down to the last two classes and cannot make the following work. This is a MS SQL Server query that I have not been able to solve through the MySQL Documentation: CREATE PROCEDURE CountPhoneNumbers AS DECLARE @count INTEGER SELECT @count = COUNT (*) FROM Customer WHERE HomePhone IS NOT NULL Print @count I have tried numerous variations of the following: CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT @count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END// // was set to be the delimiter for the creation and have tried putting various parts of the query into the parentheses. The error messages always seem to occur around the @count variable. I am using MySQL v.5.0.12 on Debian Etch. TIA, Jim Seymour -- I started using something better than the "standard" back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. "Use the Power of the Penguin" Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
