CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Michael
Michael

Reputation: 13636

Why I get error when I try to create stored procedure?

I use MSSQL-server 2012.

I create stored procedure:

CREATE PROCEDURE [dbo].[SaveEcxelReport]

 @siteNum INT = NULL,
 @dateReport DATETIME = NULL,
 @siteName NVARCHAR = NULL,
 @prog1 INT = NULL,
 @progLayout1 INT = NULL,
 @prog2 INT = NULL,
 @progLayout2 INT = NULL,
 @isLOZ BIT = NULL,
 @start DATETIME = NULL,
 @end DATETIME = NULL,
 @time DATETIME = NULL,
 @Prog1ToProg2Check REAL = NULL,
 @comment NVARCHAR = NULL

AS   
BEGIN
  SET NOCOUNT ON;
  insert into dbo.ReportTrafficDepartment(siteNum, dateReport, siteName, prog1, progLayout1, prog2, progLayout2, isLOZ, start, end, time, Prog1ToProg2Check, comment) 
         values (@siteNum,@dateReport,@siteName,@prog1,@progLayout1, @prog2,@progLayout2,@isLOZ,@start,@end,@time,@Prog1ToProg2Check,@comment) 
END

In stored procedurte in this row:

dbo.ReportTrafficDepartment(siteNum, dateReport, siteName, prog1, progLayout1, prog2, progLayout2, isLOZ, start, end, time, Prog1ToProg2Check, comment)

I get error on this word:

end

The text of the error is:

Msg 156, Level 15, State 1, Procedure SaveEcxelReport, Line 28

Incorrect syntax near the keyword 'end'.

Any idea why I get error above and how to fix it?

Upvotes: 2

Views: 72

Answers (3)

Tim Hermanson
Tim Hermanson

Reputation: 1

I've gotten into the practice of wrapping all columns and labels with square brackets. It'll save you a lot of headaches.

Upvotes: 0

Kumar_Vikas
Kumar_Vikas

Reputation: 845

end is a SQL keyword. So wrap the column name with brackets, similar to this [end]. start and time also belong to the same family. wrapping column names with brackets will indicate that this is not a keyword.

CREATE PROCEDURE [dbo].[SaveEcxelReport]

 @siteNum INT = NULL,
 @dateReport DATETIME = NULL,
 @siteName NVARCHAR = NULL,
 @prog1 INT = NULL,
 @progLayout1 INT = NULL,
 @prog2 INT = NULL,
 @progLayout2 INT = NULL,
 @isLOZ BIT = NULL,
 @start DATETIME = NULL,
 @end DATETIME = NULL,
 @time DATETIME = NULL,
 @Prog1ToProg2Check REAL = NULL,
 @comment NVARCHAR = NULL

AS   
BEGIN
  SET NOCOUNT ON;
  insert into dbo.ReportTrafficDepartment(siteNum, dateReport, siteName, prog1, progLayout1, prog2, progLayout2, isLOZ, [start], [end], [time], Prog1ToProg2Check, comment) 
         values (@siteNum,@dateReport,@siteName,@prog1,@progLayout1, @prog2,@progLayout2,@isLOZ,@start,@end,@time,@Prog1ToProg2Check,@comment) 
END

Upvotes: 2

Chanukya
Chanukya

Reputation: 5893

Add [] brackets for both time and end as below

CREATE PROCEDURE [dbo].[SaveEcxelReport]

     @siteNum INT = NULL,
     @dateReport DATETIME = NULL,
     @siteName NVARCHAR = NULL,
     @prog1 INT = NULL,
     @progLayout1 INT = NULL,
     @prog2 INT = NULL,
     @progLayout2 INT = NULL,
     @isLOZ BIT = NULL,
     @start DATETIME = NULL,
     @end DATETIME = NULL,
     @time DATETIME = NULL,
     @Prog1ToProg2Check REAL = NULL,
     @comment NVARCHAR = NULL

    AS   
    BEGIN
      SET NOCOUNT ON;
      insert into dbo.ReportTrafficDepartment(siteNum, dateReport, siteName, prog1, progLayout1, prog2, progLayout2, isLOZ, start, [end], [time], Prog1ToProg2Check, comment) 
             values (@siteNum,@dateReport,@siteName,@prog1,@progLayout1, @prog2,@progLayout2,@isLOZ,@start,@end,@time,@Prog1ToProg2Check,@comment) 
    END

Upvotes: 1

Related Questions