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]
