From: Peter Brawley
Date: September 25 2005 8:56pm
Subject: Re: Stored procedures MS SQL Server to MySQL
List-Archive: http://lists.mysql.com/mysql/189531
Message-Id: <43370F0E.3020900@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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