我正在使用 WinRDBI 在 SQL 中创建存储过程进行分配，但我无法理解为什么该过程不起作用

Question

对于我的作业，我的任务是第一次创建存储过程，我一直在使用winrdbi，因为教授要求这是我的代码：

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

我一直在第18行中获得“ query_sql。：检查输入格式”或行号上的某些变化作为错误。

我尝试编译代码，并每次都会收到一个错误。我尝试更改半殖民地的位置，删除所有分子，然后在最后只留下一个，但没有任何尝试改变任何东西。如果有人可以向我解释该语法有什么问题，以及为什么它不想编译，我会非常感谢。任何指针都会有所帮助。谢谢您的阅读。

编辑10:37 pm（4/2/2022）：我将程序放入在线编译器中，以查看是否可以更具体地找到问题。这是我在其他类似问题的建议中进行了一些编辑后，我将整个代码放入在线编译器中：

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

它仍然没有编译，但是在线编译器提供了更多信息。现在的错误是：

Error: near line 1: near "PROCEDURE": syntax error 
Error: near line 2: near "DELIMITER": syntax error 
Error: near line 9: near "DECLARE": syntax error 
Error: near line 10: near "DECLARE": syntax error 
Error: near line 11: near "DECLARE": syntax error 
Error: near line 12: near "DECLARE": syntax error 
Error: near line 13: near "DECLARE": syntax error 
Error: near line 14: near "DECLARE": syntax error 
Error: near line 16: near "INTO": syntax error 
Error: near line 18: near "INTO": syntax error 
Error: near line 25: near "INTO": syntax error 
Error: near line 27: near "INTO": syntax error 
Error: near line 33: near "INTO": syntax error 
Error: near line 38: no such column: FIRSTNAME 
Error: near line 39: near "/": syntax error

再次感谢任何帮助。谢谢。

编辑2:50 pm（20122年4月3日）：在Ramesh提出建议之后，我尝试在每个局部变量被调用或声明时将 @放置在每个本地变量后面。我仍然收到几乎相同的句法错误。这是新的代码：

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 ;

这是一个在线编译器发现的所有错误：

Error: near line 1: near "procedure": syntax error
Error: near line 3: near "DELIMITER": syntax error
Error: near line 9: near "DECLARE": syntax error
Error: near line 10: near "DECLARE": syntax error
Error: near line 11: near "DECLARE": syntax error
Error: near line 12: near "DECLARE": syntax error
Error: near line 13: near "DECLARE": syntax error
Error: near line 14: near "DECLARE": syntax error
Error: near line 16: near "INTO": syntax error
Error: near line 18: near "INTO": syntax error
Error: near line 25: near "INTO": syntax error
Error: near line 27: near "INTO": syntax error
Error: near line 33: near "INTO": syntax error
|||||
Error: near line 39: near "END$$": syntax error

我觉得这在这一点上已经变得越来越多，但是再次对任何帮助再次表示赞赏，因为今天是作业的最后一天。再次感谢！

Answer 1

在SQL Server中，您需要在变量名称之前保留 @，我认为您在选择中丢失了您的选择，请尝试使用SQL Server开发人员版本编辑器来检查语法和查询校正

DECLARE @DEPTNO AS INT

SELECT DNUMBER INTO @DEPTNO FROM DEPARTMENT