DROP TABLE food
GO
CREATE TABLE food
(product CHAR(25),
price MONEY)
GO
INSERT INTO food
VALUES("Carrot", $35.00)
INSERT INTO food
VALUES("Cheeseburger", $50.00)
INSERT INTO food
VALUES("Hamburger", $75.00)
INSERT INTO food
VALUES("Pork Chop", $175.00)
INSERT INTO food
VALUES("String Beans", $7.50)
INSERT INTO food
VALUES("Summer Squash", $11.50)
INSERT INTO food
VALUES("Sweet Potato", $13.50)
GO
DROP PROCEDURE max_price
GO
CREATE PROCEDURE max_price
@pattern VARCHAR(50),
@retval VARCHAR(10) OUTPUT
AS
BEGIN
DECLARE @max_price money
DECLARE @like_pattern VARCHAR(51)
SELECT @like_pattern = RTRIM(@pattern) + '%'
SELECT @max_price=MAX(price)
FROM food
WHERE product LIKE @like_pattern
SELECT @retval = CONVERT(VARCHAR(10), @max_price)
END
GO
