SQL Server创建存储过程时出现语法错误，请求排查解决

Hey there! Let's work through these SQL Server stored procedure syntax errors together—they’re super common once you know what to look for. Let’s break down each error and walk through how to fix them:

Error Breakdown & Fixes #

1. Msg 156 (Line 46): Syntax error near keyword 'AS' #

This almost always means something’s wrong with the code right before the AS keyword. Common culprits include:

• A trailing comma at the end of your stored procedure’s parameter list (SQL Server hates extra commas after the last parameter)
• A typo or invalid syntax in your parameter definitions (e.g., missing a data type, or misspelling a keyword)
• Extra whitespace or stray characters before AS

Check example:
Wrong:

CREATE PROCEDURE IATF_upload_exce
    @filePath VARCHAR(255),
    @uploadUser INT, -- Trailing comma here breaks the syntax
AS
BEGIN
    -- Procedure logic
END

Right:

CREATE PROCEDURE IATF_upload_exce
    @filePath VARCHAR(255),
    @uploadUser INT -- No trailing comma on the last parameter
AS
BEGIN
    -- Procedure logic
END

2. Msg 156 (Line 64): Syntax error near keyword 'SET' #

This usually happens when the SET statement is in an invalid context, or the code before it isn’t properly closed. Common issues:

• The SET is placed outside a BEGIN/END block when it should be inside (especially after conditional logic like IF/ELSE)
• The statement immediately before SET is missing a semicolon or has a syntax error, making SQL Server misinterpret where SET belongs
• A typo in the SET syntax itself (e.g., SET @var = value, instead of SET @var = value;)

3. Msg 102 (Lines 65 & 102): Syntax error near ',' #

These are classic "extra comma" errors. Look at the lines mentioned (and the line right before them) for:

• A trailing comma at the end of variable declarations (e.g., DECLARE @a INT, @b VARCHAR(50),)
• An extra comma at the end of a SELECT/INSERT column list or UPDATE SET clause
• A stray comma in a WHERE clause or parameter list for a function/stored procedure call

Example of a broken UPDATE:
Wrong:

UPDATE IATF_Table
SET Column1 = @val1,
    Column2 = @val2, -- Trailing comma here causes the error
WHERE ID = @recordID;

Right:

UPDATE IATF_Table
SET Column1 = @val1,
    Column2 = @val2 -- No trailing comma
WHERE ID = @recordID;

Step-by-Step Troubleshooting Tips #

• Use syntax highlighting: In SSMS or Azure Data Studio, syntax errors will show up with red underlines. Hover over them to get more specific hints about what’s wrong.
• Comment out code incrementally: If you’re stuck, comment out the code starting from the first error line, then uncomment sections one by one. This helps you pinpoint exactly which line or block is causing the issue.
• Validate parameter declarations first: Start with the very top of your stored procedure—fixing the AS error first might resolve the other cascading errors automatically.
• Check semicolons: While SQL Server doesn’t require semicolons for all statements, adding them after every statement can prevent context-related syntax errors, especially around SET or conditional logic.

内容的提问来源于stack exchange，提问作者kumar shivam