CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
blgrnboy
blgrnboy

Reputation: 5157

Defining a SQL Procedure

I need help with the following SQL procedure I am trying to define inside of SQL Management Studio. There are a bunch of highlights telling me that the syntax is wrong. Any help is appreciated.

Errors:

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

USE NWatchEntitiesUnitTest

CREATE PROCEDURE InsertNonExistingNode (@TableVariable dbo.NodeTypeTable READONLY, 
                                        @ScalarParameter nvarchar(255))
AS
BEGIN
INSERT INTO NWatchNodes WITH (ROWLOCK) (NodeTypeId,
      Location,
      DisplayName,
      AccessLevel,
      IsEnabled,
      CreatedOn,
      CreatedBy,
      ModifiedOn,
      ModifiedBy,
      NativeId,
      SourceId,
      Name,
      Alias) 

    SELECT Name,
    Location,
      DisplayName,
      AccessLevel,
      IsEnabled,
      CreatedOn,
      CreatedBy,
      ModifiedOn,
      ModifiedBy,
      NativeId,
      SourceId,
      Name,
      Alias

    FROM @TableVariable t
        /*Left Join then where ID is null to make sure the record doesn't exists*/
        LEFT JOIN NWatchNodes PR WITH (NOLOCK) 
                                ON  PR.ID = @ScalarParameter
                                AND PR.Name = t.Name
        WHERE PR.ID IS NULL
END

GO

Upvotes: 1

Views: 55

Answers (1)

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

Error text is self-explanatory. Since CREATE PROCEDURE should be first statement in a batch - then just add GO after USE statement, thus you will break it to the two batches - first one setting current DB and second one creating procedure itself.

USE NWatchEntitiesUnitTest
GO
CREATE PROCEDURE

Upvotes: 2

Related Questions