SQL Server Incorrect Syntax: Create/Alter Procedure must be the first statement in a query batch

ohfgkhjo 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(132)

This question has been asked before, but it all involved using "go" which I am not in need of here, at least I believe so.

I am following this tut https://www.youtube.com/watch?v=-xMGwiV5A6o , near the 1:25 mark exactly. And his seems to execute while mine doesn't:

Select * From Snacks

    Create Proc spGetSnackByID
    @Id int
    as

    Begin
    Select Id, Name, Location
    from Snacks where Id = @Id
    End

Here is the exact error, being highlighted with the "BEGIN" statement:

"Msg 111, Level 15, State 1, Procedure spGetSnackByID, Line 7
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch."

sql-server

来源：https://stackoverflow.com/questions/34773473/incorrect-syntax-create-alter-procedure-must-be-the-first-statement-in-a-query

2条答案

按热度按时间

7kjnsjlb1#

If you want to keep the script as it is (select followed by a create procedure), you can construct the creation of the stored procedure in a NVARCHAR and EXECUTE it using sp_executesql . This way the CREATE statement is the first statement. Like this:

Select * From Snacks

EXECUTE sp_executesql N'
  Create Proc spGetSnackByID
  @Id int
  as
  Begin
    Select Id, Name, Location
    from Snacks where Id = @Id
  End
';

赞(0）回复(0）举报 2023-10-15

weylhg0b2#

Yes, SQL Server wants the create procedure as the first line. Just remark out the select above, it is not what you want anyway, because you have specified the fields in the stored procedure.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Incorrect Syntax: Create/Alter Procedure must be the first statement in a query batch

2条答案

相关问题

热门标签

最新问答