gistfile1.txt

CREATE TABLE IF NOT EXISTS `offices` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `city` varchar(22) NOT NULL,
 `country` varchar(255) NOT NULL,
 `phone` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


INSERT INTO `offices` (`id`, `city`, `country`, `phone`) VALUES
(1, 'Nashville', 'USA', '6155139185'),
(2, 'Dallas', 'USA', '5555555555'),
(3, 'New York', 'USA', '5555555555');

DELIMITER // 
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255), IN limitNumber INTEGER(5), IN offsetNumber INTEGER(5)) 
BEGIN 
SELECT SQL_CALC_FOUND_ROWS city, phone 
FROM offices 
WHERE country = countryName 
LIMIT limitNumber OFFSET offsetNumber;
END // 
DELIMITER ;

mysql> CREATE TABLE IF NOT EXISTS offices (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> city varchar(22) NOT NULL,
-> country varchar(255) NOT NULL,
-> phone varchar(255) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO offices (id, city, country, phone) VALUES
-> (1, 'Nashville', 'USA', '6155139185'),
-> (2, 'Dallas', 'USA', '5555555555'),
-> (3, 'New York', 'USA', '5555555555');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> DELIMITER //
mysql> CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255), IN limitNumber INTEGER(5), IN offsetNumber INTEGER(5))
-> BEGIN
-> SELECT SQL_CALC_FOUND_ROWS city, phone
-> FROM offices
-> WHERE country = countryName
-> LIMIT limitNumber OFFSET offsetNumber;
-> END //
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> CALL GetOfficeByCountry;
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE opensky_devo.GetOfficeByCountry; expected 3, got 0
mysql> CALL GetOfficeByCountry('USA',2,1);
+----------+------------+
| city | phone |
+----------+------------+
| Dallas | 5555555555 |
| New York | 5555555555 |
+----------+------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL GetOfficeByCountry('USA',1,2);
+----------+------------+
| city | phone |
+----------+------------+
| New York | 5555555555 |
+----------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)