存储过程的语法错误

一尘不染

存储过程的语法错误

sql

USE AdventureWorks2019;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE dbo.EmployeeGenderbyJobTitle
AS
BEGIN
    SET NOCOUNT ON;

    DROP TABLE IF EXISTS #EmployeeGenderbyJobTitle
    GO 

    CREATE TABLE #EmployeeGenderbyJobTitle
    (
        EmployeeID int NOT NULL PRIMARY KEY,
        Gender nchar(1),
        JobTitle nvarchar(50)
    )
    GO

    INSERT INTO #EmployeeGenderbyJobTitle (Gender, JobTitle)
        SELECT Gender, JobTitle
        FROM humanresources.employee AS e
    GO

    SELECT COUNT(*) AS FemaleEmployees, JobTitle
    FROM #EmployeeGenderbyJobTitle
    WHERE Gender = 'F' 
    GROUP BY JobTitle
    GO

    SELECT COUNT(*) AS MaleEmployees, JobTitle
    FROM #EmployeeGenderbyJobTitle
    WHERE Gender = 'M' 
    GROUP BY JobTitle
END;

当我自己保留过程的代码时，查询运行没有问题。一旦我将它粘贴在开始和结束 GO 之间，drop table 就会出现语法错误。我试过重新排列代码、编辑、玩分号，但它不会消失。还有一个语法错误，期待在 END 后面的分号上进行对话，我无法弄清楚。

阅读 67

2022-07-22

一尘不染

不知道为什么你首先需要这里的临时表。您可以通过一些条件聚合大大简化这一点。这将按职位返回性别计数。

CREATE OR ALTER PROCEDURE dbo.EmployeeGenderbyJobTitle
AS
BEGIN
    SET NOCOUNT ON;

    select Sum(case when e.Gender = 'M' then 1 end) as MaleEmployees
        , Sum(case when e.Gender = 'F' then 1 end) as FemaleEmployees
        , e.JobTitle
    from humanresources.employee AS e
    group by e.JobTitle
END

2022-07-22

一尘不染

GO在 SQL Server 中发出批处理结束的信号，因此您不能go在批处理中间使用（aprocedure等function）

参考SQL Server 文档

2022-07-22

存储过程的语法错误

共2个答案