Get a list of inserted ids as a result set from a stored procedure T-SQL

user_18085110 0
Viewed 54

I have generated a stored procedure that inserts values to the ITEM table from JSON string. Just like that:

CREATE PROCEDURE SP_INSERT_JSON
        @J NVARCHAR(MAX)
AS
BEGIN
    SET IDENTITY_INSERT ITEM ON;
    INSERT INTO ITEM (ITEM_ID, TITLE, DESCRIPTION)
    SELECT (SELECT IDENT_CURRENT('ITEM') + 1), *
    FROM OPENJSON(@J, '$')
    WITH(
            Title NVARCHAR(MAX) '$.Title', 
            Description NVARCHAR(MAX) '$.Description'
        )
    SET IDENTITY_INSERT ITEM OFF;
END

Also, what I want now is to return a list of inserted IDs as a result set from this stored procedure. I've tried to combinate it with an OUTPUT clause in different ways but it never worked. Any idea how to do this?

CREATE TABLE [ITEM]
(
    [ITEM_ID]   INT   IDENTITY(1,1) NOT NULL,
    [TITLE] VARCHAR(255) NULL,
    [DESCRIPTION] VARCHAR(255) NULL,
    CONSTRAINT PK__ITEM PRIMARY KEY CLUSTERED ([ITEM_ID] ASC)
)

JSON STRING: N'[{"Title" : "Introduction to Computers","Description" : "Computer literacy. Basic Computer Concepts."}]'
output stored-procedures tsql sql json
1 Answers

I'm not sure why you are inserting into the Identity column, you don't need to just don't include it in the column list.

You can output the newly inserted Item_Id using output:

insert into ITEM (TITLE, DESCRIPTION)
output inserted.ITEM_ID
select Title, Description
from OpenJson(@j, '$')
with(
        Title nvarchar(max) '$.Title', 
        Description nvarchar(max) '$.Description'
    )

See demo Fiddle

Also note that prefixing procedures with sp_ is not recommended, System Procedures are reserved by Microsoft.

user_153326500
Related