0

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

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:

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.

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.

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

CREATE PROCEDURE permission denied in database 'master'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tobin
  • 1,698
  • 15
  • 24

1 Answers1

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.

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.

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

Tobin
  • 1,698
  • 15
  • 24
  • 1
    In most languages/environments, one can (and usually does) specify the database to use when establishing a connection. That is a good habit to develop for many reasons - this is just one. – SMor Oct 04 '17 at 15:37
  • Good point, the connection is set to Master; adding procedures to different DBs should one disconnect and reconnect? – Tobin Oct 04 '17 at 18:19