CODEWITHSUNDEEP
sqlsql-servermerge
Simon Price
Simon Price

Reputation: 3261

Output Always 1

I have a stored procedure and am using a Merge Statement to Insert and Update. This aspect is working as I require.

However, the output when inserting the record is always 1 and I cannot see why? I would be grateful if someone could review this procedure and let me know what I could be doing wrong,. 

CREATE PROCEDURE [dbo].[FileAdd]

@FileId int, 
@FileData varbinary(max), 
@ContentType Varchar(100),
@OperatorId int

AS
BEGIN
--In Memory Table to 
DECLARE @MergeOutput TABLE
(
  Id INT
);

--Merge needs a table to Merge with so using a CTE
WITH CTE AS (
SELECT @FileId as FileId)

--Merge
MERGE INTO [dbo].[Files] as T
USING CTE AS S
ON T.FileId = S.FileId
WHEN NOT MATCHED THEN 
    INSERT (
    FileData,
    ContentType,
    OperatorIdCreated, 
    OperatorIdUpdated
    ) 
    VALUES(
    @FileData,
    @ContentType,
    @OperatorId, 
    @OperatorId
    )
WHEN MATCHED THEN 
    UPDATE SET 
    FileData = @FileData, 
    ContentType= @ContentType,
    OperatorIdUpdated = @OperatorId, 
    Updated = GetDate()

OUTPUT
INSERTED.FileId
INTO @MergeOutput;

SELECT * FROM @MergeOutput;
END
GO

Upvotes: 2

Views: 254

Answers (1)

S3S
S3S

Reputation: 25142

The reason you are getting 1 is because that is what is being UPDATED or INSERTED. When it's the UPDATED value, then it is the value are passing into @FileID.

With the OUTPUT clause:

INSERTED Is a column prefix that specifies the value added by the insert or update operation.

Thus, what ever value is UPDATED (which is @FileID) or INSERTED (which will be whatever your FileID table logic is) this will be returned in your code. If you are always getting 1, then you must me always updating the column for FileID = 1.

Changing your bottom to inserted.* would show you this, as it would OUTPUT the updated row.

Check the demo here.

Upvotes: 2

Related Questions