CODEWITHSUNDEEP
mysqlstored-procedures
Austin Johnston
Austin Johnston

Reputation: 219

error in stored procedure syntax

CREATE PROCEDURE getNumbers
(
@dName VARCHAR(20),
@iNum INT OUTPUT,
@sNum INT OUTPUT
)
AS
BEGIN
@iNum=SELECT count(i.ID) FROM instructor WHERE dept_name=@dName
@sNum=SELECT count(s.ID) FROM student AS s WHERE dept_name=@dName
END

Here is my first attempt at making a stored procedure. I keep getting an error in syntax at the line: @dName VARCHAR(20),

I have looked at other problems and tried their solutions but nothing works!

Upvotes: 1

Views: 75

Answers (2)

valex
valex

Reputation: 24134

Is it MSSQL ? If so you don't need to use brackets and you should use following syntax to assign variables:

CREATE PROCEDURE getNumbers
@dName VARCHAR(20),
@iNum INT OUTPUT,
@sNum INT OUTPUT
AS
BEGIN
SELECT @iNum=count(i.ID) FROM instructor WHERE dept_name=@dName;
SELECT @sNum=count(s.ID) FROM student AS s WHERE dept_name=@dName;
END

If you do it in MYSql

DELIMITER $$     
CREATE PROCEDURE getNumbers(
    IN dName VARCHAR(20),
    OUT iNum INT,
    OUT sNum INT)
    BEGIN
    SELECT count(i.ID)  INTO iNum FROM instructor WHERE dept_name=dName;
    SELECT count(s.ID) INTO sNum FROM student AS s WHERE dept_name=dName;
    END$$ 

DELIMITER ;

Upvotes: 1

niculare
niculare

Reputation: 3687

Try this form:

CREATE PROCEDURE getNumbers
@dName VARCHAR(20),
@iNum INT OUTPUT,
@sNum INT OUTPUT
AS
BEGIN

Upvotes: 1

Related Questions