title: I'm creating stored procedure in SQL using WinRDBI for an assignment and I can't understand why the procedure doesn't work
body: <p>For my assignment I was tasked with creating a Stored Procedure for the first time, I have been using WinRDBI since it is required by the professor here is my code here is my code:</p>
  <pre><code>CREATE PROCEDURE uspGetDeptStats(
      @DEPARTMENTNAME VARCHAR(15))
  AS
  BEGIN
  SET NOCOUNT ON
      DECLARE @FIRSTNAME AS VARCHAR(25)
      DECLARE @LASTNAME  AS VARCHAR(25)
      DECLARE @NO_OF_EMPLOYEES AS INT
      DECLARE @TOTAL_SALARY AS INT
      DECLARE @TOTAL_NO_OF_DEPENDENTS AS INT
      DECLARE @TOTAL_HOURS AS DECIMAL(5,1)
      DECLARE @DEPTNO AS INT
      SELECT DNUMBER INTO DEPTNO FROM DEPARTMENT WHERE DNAME = DEPARTMENTNAME
      SELECT
          FNAME, LNAME INTO FIRSTNAME, LASTNAME
      FROM DEPARTMENT D
      JOIN EMPLOYEE E ON D.MGRSSN = E.SSN
      WHERE DNUMBER = DEPTNO;
      SELECT FIRSTNAME,LASTNAME,NO_OF_EMPLOYEES,TOTAL_SALARY,TOTAL_NO_OF_DEPENDENTS, TOTAL_HOURS;
  END
  </code></pre>
  <p>I keep getting &quot;query_sql.: Check input format at line 18&quot; or some variation on the line number as an error.</p>
  <p>I tried compiling the code and I received an error every time. I tried changing where the semicolons were, removing all of them, and then leaving only one at the end and none of the attempts changed anything. If anyone could please explain to me what is wrong with the syntax and why it doesn't want to compile I would greatly appreciate it. Any pointers at all would help. Thank you for reading.</p>
  <p>EDIT 10:37PM(4/2/2022): I put my program into an online compiler in order to see if I could find what the problems were more specifically. Here is the entire code I dropped into the online compiler after some edits from suggestions I found on other similar problems:</p>
  <pre><code>DROP PROCEDURE IF EXISTS `uspGetDeptStats`;
  DELIMITER //
  -- start of procedure creation
  CREATE PROCEDURE `uspGetDeptStats` (
  in DEPARTMENTNAME VARCHAR(15) )
  BEGIN
  -- declare local variables
  DECLARE FIRSTNAME VARCHAR(25);
  DECLARE LASTNAME VARCHAR(25);
  DECLARE NO_OF_EMPLOYEES INT;
  DECLARE TOTAL_SALARY INT;
  DECLARE TOTAL_NO_OF_DEPENDENTS INT;
  DECLARE TOTAL_HOURS DECIMAL(5,1);
  DECLARE DEPTNO INT;
  -- get the department number
  SELECT DNUMBER INTO DEPTNO FROM DEPARTMENT WHERE DNAME = DEPARTMENTNAME;
  -- get firstname and lastname of the manager of the department
  SELECT
  FNAME , LNAME INTO FIRSTNAME,LASTNAME
  FROM DEPARTMENT D
  JOIN EMPLOYEE E ON D.MGRSSN = E.SSN
  WHERE DNUMBER = DEPTNO ;
  
  -- to get the number of employees and total salary of all employees work for this department
  SELECT COUNT(*) , SUM(SALARY) INTO NO_OF_EMPLOYEES,TOTAL_SALARY FROM EMPLOYEE WHERE DNO = DEPTNO;
  -- to get the total number of dependents of all employees who work for this department
  SELECT
  COUNT(*) INTO TOTAL_NO_OF_DEPENDENTS
  FROM EMPLOYEE E
  JOIN DEPENDENT D ON D.ESSN = E.SSN
  WHERE DNO=DEPTNO;
  -- to get the total weekly work hours of employees who belong to this department and who work on the projects that are controlled by this department
  SELECT SUM(HOURS) INTO TOTAL_HOURS FROM WORKS_ON W
  JOIN PROJECT P ON P.PNUMBER = W.PNO
  JOIN EMPLOYEE E ON E.SSN = W.ESSN
  WHERE DNO =DEPTNO AND DNUM = DEPTNO;
  -- displaying all result
  SELECT FIRSTNAME,LASTNAME,NO_OF_EMPLOYEES,TOTAL_SALARY,TOTAL_NO_OF_DEPENDENTS, TOTAL_HOURS;
  END //
  DELIMITER ;
  -- end of procedure
  </code></pre>
  <p>It still doesn't compile but the online compiler gives more information. The errors now are:</p>
  <pre><code>Error: near line 1: near &quot;PROCEDURE&quot;: syntax error 
  Error: near line 2: near &quot;DELIMITER&quot;: syntax error 
  Error: near line 9: near &quot;DECLARE&quot;: syntax error 
  Error: near line 10: near &quot;DECLARE&quot;: syntax error 
  Error: near line 11: near &quot;DECLARE&quot;: syntax error 
  Error: near line 12: near &quot;DECLARE&quot;: syntax error 
  Error: near line 13: near &quot;DECLARE&quot;: syntax error 
  Error: near line 14: near &quot;DECLARE&quot;: syntax error 
  Error: near line 16: near &quot;INTO&quot;: syntax error 
  Error: near line 18: near &quot;INTO&quot;: syntax error 
  Error: near line 25: near &quot;INTO&quot;: syntax error 
  Error: near line 27: near &quot;INTO&quot;: syntax error 
  Error: near line 33: near &quot;INTO&quot;: syntax error 
  Error: near line 38: no such column: FIRSTNAME 
  Error: near line 39: near &quot;/&quot;: syntax error
  </code></pre>
  <p>Once again any assistance is much appreciated. Thank you.</p>
  <p>EDIT 2:50PM(4/3/2022): After a suggestion from Ramesh I tried putting an @ behind every local variable whenever it is called or declared. I still receive practically the same syntactical errors. Here is the new code:</p>
  <pre><code>DROP procedure IF EXISTS `uspGetDeptStats`;
  -- start of procedure creation
  DELIMITER $$
  CREATE PROCEDURE `uspGetDeptStats` (
  in DEPARTMENTNAME VARCHAR(15) )
  BEGIN
  -- declare local variables
  DECLARE @FIRSTNAME VARCHAR(25);
  DECLARE @LASTNAME VARCHAR(25);
  DECLARE @NO_OF_EMPLOYEES INT;
  DECLARE @TOTAL_SALARY INT;
  DECLARE @TOTAL_NO_OF_DEPENDENTS INT;
  DECLARE @TOTAL_HOURS DECIMAL(5,1);
  DECLARE @DEPTNO INT;
  -- get the department number
  SELECT DNUMBER INTO @DEPTNO FROM DEPARTMENT WHERE DNAME = @DEPARTMENTNAME;
  -- get firstname and lastname of the manager of the department
  SELECT
  FNAME , LNAME INTO @FIRSTNAME,@LASTNAME
  FROM DEPARTMENT D
  JOIN EMPLOYEE E ON D.MGRSSN = E.SSN
  WHERE DNUMBER = @DEPTNO;
  
  -- to get the number of employees and total salary of all employees work for this department
  SELECT COUNT(*) , SUM(SALARY) INTO @NO_OF_EMPLOYEES,@TOTAL_SALARY FROM EMPLOYEE WHERE DNO = @DEPTNO;
  -- to get the total number of dependents of all employees who work for this department
  SELECT
  COUNT(*) INTO @TOTAL_NO_OF_DEPENDENTS
  FROM EMPLOYEE E
  JOIN DEPENDENT D ON D.ESSN = E.SSN
  WHERE DNO=@DEPTNO;
  -- to get the total weekly work hours of employees who belong to this department and who work on the projects that are controlled by this department
  SELECT SUM(HOURS) INTO @TOTAL_HOURS FROM WORKS_ON W
  JOIN PROJECT P ON P.PNUMBER = W.PNO
  JOIN EMPLOYEE E ON E.SSN = W.ESSN
  WHERE DNO =DEPTNO AND DNUM = DEPTNO;
  -- displaying all result
  SELECT @FIRSTNAME,@LASTNAME,@NO_OF_EMPLOYEES,@TOTAL_SALARY,@TOTAL_NO_OF_DEPENDENTS, @TOTAL_HOURS;
  END$$
  DELIMITER ;
  </code></pre>
  <p>And here are all the errors that were found by an online compiler:</p>
  <pre><code>Error: near line 1: near &quot;procedure&quot;: syntax error
  Error: near line 3: near &quot;DELIMITER&quot;: syntax error
  Error: near line 9: near &quot;DECLARE&quot;: syntax error
  Error: near line 10: near &quot;DECLARE&quot;: syntax error
  Error: near line 11: near &quot;DECLARE&quot;: syntax error
  Error: near line 12: near &quot;DECLARE&quot;: syntax error
  Error: near line 13: near &quot;DECLARE&quot;: syntax error
  Error: near line 14: near &quot;DECLARE&quot;: syntax error
  Error: near line 16: near &quot;INTO&quot;: syntax error
  Error: near line 18: near &quot;INTO&quot;: syntax error
  Error: near line 25: near &quot;INTO&quot;: syntax error
  Error: near line 27: near &quot;INTO&quot;: syntax error
  Error: near line 33: near &quot;INTO&quot;: syntax error
  |||||
  Error: near line 39: near &quot;END$$&quot;: syntax error
  </code></pre>
  <p>I feel like it is getting redundant at this point but once again any help at all is much appreciated as today is the final day for the assignment. Thanks again!</p>