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'.
LVL 8
mani_saiAsked:
Who is Participating?
 
Larso76Commented:
Change script to

CREATE PROCEDURE dbo.AcpReport_HR_CoilProduction
     
AS
BEGIN
     
      SET NOCOUNT ON;
   
     create TABLE #Braze1shift (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
0
 
Lee SavidgeCommented:
SQL 2000 doesn't have table variables. You need to use temporary tables as per the previous comment.
0
 
mani_saiAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.