Trouble with OUTPUT statement. Incorrect Syntax

Question

I am trying to update a data set based on one conditional and then retrieving all the updated rows. VS keeps telling me there is an incorrect syntax error near my OUTPUT clause but I do not see anything wrong. I am just trying to figure out how to use "OUTPUT" so this may be a very stupid mistake I am making but failing to see.

What is wrong (syntactically) with this OUTPUT clause?

CREATE PROCEDURE [dbo].[GetInitialSessionNotifications]
@CurrentSessionId bigint
AS
DECLARE @tempTable table(
id bigint NOT NULL,
[Type] nvarchar,
DocumentCommentID bigint,
AnnouncmentID int,
EventID int,
MeetingID int,
[Read] bit,
RecieverId int,
AnnouncmentCommentId bigint,
EventCommentId bigint,
MeetingCommentId bigint,
DateAndTime DateTime);

UPDATE Notifications SET SessionId = @CurrentSessionId
WHERE SessionId != @CurrentSessionId
OUTPUT INSERTED.id,
INSERTED.[Type],
INSERTED.DocumentCommentID,
INSERTED.AnnouncmentID,
INSERTED.EventID,
INSERTED.MeetingID,
INSERTED.[Read],
INSERTED.RecieverId,
INSERTED.AnnouncmentCommentId,
INSERTED.EventCommentId,
INSERTED.MeetingCommentId,
INSERTED.DateAndTime
INTO @tempTable;

SELECT id, [Type], DocumentCommentId, AnnouncmentID, EventID, MeetingID,
[Read], RecieverId, AnnouncmentCommentId, EventCommentId, MeetingCommentId, DateAndTime
FROM @tempTable;

RETURN 0

@Adrian Exactly, UPDATE Notifications SET SessionId = @CurrentSessionId OUTPUT INSERTED.id, INSERTED.[Type], ... INSERTED.DateAndTime INTO @tempTable WHERE SessionId != @CurrentSessionId; — Joachim Isaksson
– Joachim Isaksson, Commented May 12, 2013 at 17:52
Also: you should always define a length when declaring a (n)varchar column! So this here: [Type] nvarchar, might end up giving you a 1 character long string column - is that what you want? — marc_s
– marc_s, Commented May 12, 2013 at 18:31
From Books Online: UPDATE ... [ <OUTPUT Clause> ] [ FROM{ <table_source> } [ ,...n ] ] [ WHERE { <search_condition> ... — Bogdan Sahlean
– Bogdan Sahlean, Commented May 12, 2013 at 18:54
@BogdanSahlean I'm still trying to wrap my head around complex sql queries. I have been teaching myself for the past few years and was used to the simple select, update, and insert queries. Now its getting more exciting that I know how to use joins, batch queries and procession with in sql server. — Adrian
– Adrian, Commented May 13, 2013 at 0:22

Devart · Accepted Answer · 2013-05-13 05:28:20Z

Try this one -

CREATE PROCEDURE [dbo].[GetInitialSessionNotifications]

@CurrentSessionId BIGINT

AS BEGIN

    DECLARE @tempTable TABLE
    (
        id BIGINT NOT NULL ,
        [Type] NVARCHAR ,
        DocumentCommentID BIGINT ,
        AnnouncmentID INT ,
        EventID INT ,
        MeetingID INT ,
        [Read] BIT ,
        RecieverId INT ,
        AnnouncmentCommentId BIGINT ,
        EventCommentId BIGINT ,
        MeetingCommentId BIGINT ,
        DateAndTime DATETIME
    )

    UPDATE  Notifications
    SET     SessionId = @CurrentSessionId
    OUTPUT  
        INSERTED.id ,
        INSERTED.[Type] ,
        INSERTED.DocumentCommentID ,
        INSERTED.AnnouncmentID ,
        INSERTED.EventID ,
        INSERTED.MeetingID ,
        INSERTED.[Read] ,
        INSERTED.RecieverId ,
        INSERTED.AnnouncmentCommentId ,
        INSERTED.EventCommentId ,
        INSERTED.MeetingCommentId ,
        INSERTED.DateAndTime
    INTO @tempTable
    WHERE   SessionId != @CurrentSessionId

    SELECT  id ,
            [Type] ,
            DocumentCommentId ,
            AnnouncmentID ,
            EventID ,
            MeetingID ,
            [Read] ,
            RecieverId ,
            AnnouncmentCommentId ,
            EventCommentId ,
            MeetingCommentId ,
            DateAndTime
    FROM    @tempTable;

END

Collectives™ on Stack Overflow

Trouble with OUTPUT statement. Incorrect Syntax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related