sql - “CREATE PROCEDURE”必须是批处理中的唯一语句(错误)

Question

这个问题已经有答案了:

Incorrect Syntax: Create Procedure must be the only statement in the batch (2 个回答)

已关闭 6 年前。

我正在创建此表，但第一个过程 (sp_joseview) 遇到问题

create table josecustomer(
name varchar(50),
address varchar(300),
ssnid int,
balance bigint,
accountnumber bigint
)

insert into josecustomer values('Aman','Canada',10000,5000,100000000)
insert into josecustomer values('Shubham','USA',10001,6000,200000000)
insert into josecustomer values('Himanshu','Australia',10002,2000,300000000)
insert into josecustomer values('Jose','India',10003,3000,400000000)
insert into josecustomer values('Albert','Brazil',10004,4000,500000000)
insert into josecustomer values('Peterson','Germany',10005,7000,600000000)
insert into josecustomer values('Adam','Honkong',10006,8000,700000000)
insert into josecustomer values('William','Paris',10007,9000,800000000)

select * from josecustomer

create proc sp_joseview
as begin
select * from josecustomer
end
go

create proc sp_updatejose
(@accountno bigint,@newbalance bigint)
as begin
update josecustomer 
set balance=@newbalance
where accountnumber=@accountno
end 
go

第一个过程显示语法错误，但我无法弄清楚该错误可能是什么。

Answer 1

该错误是不言自明的 - 您无法发出 CREATE PROCEDURE 语句，除非它是批处理中的唯一语句。

在 SSMS 中，GO 关键字将语句拆分为单独的批处理，因此您需要在 CREATE PROCEDURE 之前的语句后面添加一个:

create table josecustomer(
name varchar(50),
address varchar(300),
ssnid int,
balance bigint,
accountnumber bigint
)

insert into josecustomer values('Aman','Canada',10000,5000,100000000)
insert into josecustomer values('Shubham','USA',10001,6000,200000000)
insert into josecustomer values('Himanshu','Australia',10002,2000,300000000)
insert into josecustomer values('Jose','India',10003,3000,400000000)
insert into josecustomer values('Albert','Brazil',10004,4000,500000000)
insert into josecustomer values('Peterson','Germany',10005,7000,600000000)
insert into josecustomer values('Adam','Honkong',10006,8000,700000000)
insert into josecustomer values('William','Paris',10007,9000,800000000)

select * from josecustomer
Go  --Add a Go here

create proc sp_joseview
as begin
select * from josecustomer
end
go

create proc sp_updatejose
(@accountno bigint,@newbalance bigint)
as begin
update josecustomer 
set balance=@newbalance
where accountnumber=@accountno
end 
go