SQL Server:在DB中创建过程而不使用[GO]

时间:2022-03-13 15:40:41

I want to execute this (simplified) query using node-mssql that executes in SQL Server 2017 fine:

我想使用在SQL Server 2017中执行的node-mssql执行此(简化)查询:

USE [Journal]
[GO]

CREATE PROCEDURE [dbo].[EventDelete]
    @NotificationID INT
AS
    DELETE Notification
    WHERE NotificationID = @NotificationID
[GO]

node-mssql declares syntax error using [GO] and requires semicolon, therefore I try this:

node-mssql使用[GO]声明语法错误并需要分号,因此我尝试这样做:

USE [Journal];

CREATE PROCEDURE [dbo].[EventDelete]
    @NotificationID INT
AS
    DELETE Notification
    WHERE NotificationID = @NotificationID;

Now we get error:

现在我们得到错误:

CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

CREATE / ALTER PROCEDURE'必须是查询批处理中的第一个语句。

So let's try this:

所以让我们试试这个:

CREATE PROCEDURE [Journal].[dbo].[EventDelete]
    @NotificationID INT
AS
    DELETE Notification
    WHERE NotificationID = @NotificationID;

Now we get

现在我们得到了

RequestError: 'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.

RequestError:'CREATE / ALTER PROCEDURE'不允许将数据库名称指定为对象名称的前缀。

Naturally without any DB declaration it attempts to attach to the master error:

当然没有任何数据库声明它会尝试附加到主错误:

CREATE PROCEDURE permission denied in database 'master'.

数据库“master”中的CREATE PROCEDURE权限被拒绝。

1 个解决方案

#1


0  

So writing the question really works at setting one's thoughts straight.

所以写这个问题确实可以直接设定一个人的想法。

The reason is the stored procedure requires to be created in one batch, which [GO] signifies, with nothing else.

原因是存储过程需要在一个批处理中创建,[GO]表示没有别的。

Execute USE [Journal] as one batch using the .batch('USE [Journal]') method and then the SQL to CREATE PROCEDUCE as a new .batch(...) execution, sequentially.

使用.batch('USE [Journal]')方法将USE [Journal]作为一个批处理执行,然后依次将SQL作为新的.batch(...)执行执行CREATE PROCEDUCE。

Unless there is another method within node-mssql which allows for multi-batch executions?

除非node-mssql中有另一种方法允许多批处理执行?

#1


0  

So writing the question really works at setting one's thoughts straight.

所以写这个问题确实可以直接设定一个人的想法。

The reason is the stored procedure requires to be created in one batch, which [GO] signifies, with nothing else.

原因是存储过程需要在一个批处理中创建,[GO]表示没有别的。

Execute USE [Journal] as one batch using the .batch('USE [Journal]') method and then the SQL to CREATE PROCEDUCE as a new .batch(...) execution, sequentially.

使用.batch('USE [Journal]')方法将USE [Journal]作为一个批处理执行,然后依次将SQL作为新的.batch(...)执行执行CREATE PROCEDUCE。

Unless there is another method within node-mssql which allows for multi-batch executions?

除非node-mssql中有另一种方法允许多批处理执行?