Avatar of Mani Pazhana
Mani Pazhana
Flag for United States of America asked on

SQL 2000 - Query Syntax Error

Hello Experts,

I am getting this error on SQL Server 2000:

CREATE PROCEDURE dbo.AcpReport_HR_CoilProduction
      
AS
BEGIN
      
      SET NOCOUNT ON;
   
      DECLARE @Braze1Shift TABLE(SNo INT NOT NULL IDENTITY(1,1), JobTitle Varchar(255) NOT NULL ,   Name Varchar(255) NOT NULL ,  NUM Varchar(255) NOT NULL  );

      SET IDENTITY_INSERT @Braze1Shift ON;
      
      Insert Into @Braze1Shift(JobTitle,Name,NUM )
      SELECT JobTitle, LastName + ',' + FirstName as 'Name', NUM
      FROM [AIO_Test_Results].[dbo].[EmployeeInfo] Where [Group] = 'PRODUCTION'
      and Dept = 'BRAZE' and TRAIN IS NULL and Jobtitle <> 'LEAD' AND Shift = 1
      Order By LastName

      SET IDENTITY_INSERT @Braze1Shift OFF;

      Select * from @Braze1Shift;
   
   
END
GO



Error:

Msg 170, Level 15, State 1, Procedure AcpReport_HR_CoilProduction, Line 14
Line 14: Incorrect syntax near '@Braze1Shift'.

Msg 170, Level 15, State 1, Procedure AcpReport_HR_CoilProduction, Line 21
Line 21: Incorrect syntax near '@Braze1Shift'.
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Mani Pazhana

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Larso76

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Lee

SQL 2000 doesn't have table variables. You need to use temporary tables as per the previous comment.
Mani Pazhana

ASKER
Thanks
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy