creating a stored procedure based on view with parameter in MYSQL

user_13189955 0
Viewed 37

Im getting a syntax error 1064 in mysqlworkbench for the stored procedure im trying to create. the stored procedure is based on a view i created.

this is my view based on dataset(https://www.kaggle.com/gregorut/videogamesales) : 

Create View CodSales AS 
SELECT 
    Name, Platform, Year, Global_Sales
FROM
   vgsales
WHERE
   Name LIKE 'Call of Duty:%'
ORDER BY Global_Sales DESC;

and this is the stored procedure based on View with one parameter: 

CREATE PROCEDURE CallofDuty_Sales @Name varchar(25) 
AS
SELECT * FROM CodSales WHERE Name = @Name
GO;

Exec CallofDuty_Sales @Name = 'Call of Duty: Black Ops'

I did the stored procedure based on the example shown in https://www.w3schools.com/sql/sql_stored_procedures.asp

mysql sql
1 Answers

Your code is for sql server , which doesn't work in mysql

It must be

DELIMITER //
CREATE PROCEDURE `CallofDuty_Sales` (IN _Name varchar(25))
BEGIN
SELECT 
    Name, Platform, Year, Global_Sales
FROM
   vgsales
WHERE
   Name LIKE CONCAT(_Name,'%')
ORDER BY Global_Sales DESC;
END//
DELIMITER ;

CALL CallofDuty_Sales ('Call of Duty: Black Ops');
user_51935360
Related