MySQL ODBC Connector - Stored procedures MS SQL Server to MySQL

Author

Stored procedures MS SQL Server to MySQL

Jim Seymour

2005-09-25, 8:23 pm

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/mysql? unsub...sie.nctu.edu.tw

Peter Brawley

2005-09-25, 8:23 pm

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_routin
e_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_routin
e_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_routin
e_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/mysql? unsub...sie.nctu.edu.tw

Jim Seymour

2005-09-26, 11:23 am

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_routin
e_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/mysql? unsub...sie.nctu.edu.tw

Sponsored Links