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;
When I keep the procedure’s code by itself, the query runs no issue. Once I paste it in between begin and end the GO after drop table has a syntax error. I’ve tried rearranging the code, editing, playing with the semi colons and it won’t go away. There’s also a syntax error expecting conversation on the semicolon behind END that I can’t figure out.
>Solution :
GO signals the end of a batch in SQL Server so you can’t use go in middle of a batch (a procedure, function etc.)
Reference SQL Server docs