CODEWITHSUNDEEP
sql-server
michal.jakubeczy
michal.jakubeczy

Reputation: 9469

Read INSERT output from stored procedure

I have a following stored procedure

CREATE PROCEDURE [dbo].[InsertCategory]
  @Name nvarchar(100)
AS  
BEGIN 
    INSERT INTO [dbo].[Category]([Name])
    OUTPUT INSERTED.CategoryId, INSERTED.[Timestamp]
    VALUES (@Name)
END

And I call it like this:

EXEC [dbo].[InsertCategory] @Name= @Name

I would like to know what the id of inserted Category is (it is output in insert statement). It is written to the output, but I can't figure out how to assign it to a variable without modifying stored procedure. In C# I can use command.ExecuteReader and I get it, but I do not know how to get it in SQL Server.

I also cannot use SCOPE_IDENTITY as we have our own system of generating ids.

Upvotes: 4

Views: 1477

Answers (2)

TriV
TriV

Reputation: 5148

You can Declare a table and insert output into it.

CREATE PROCEDURE [dbo].[InsertCategory]
  @Name nvarchar(100)
AS  
BEGIN 
   DECLARE @Result AS TABLE (
         CategoryId int,
         TimeStamp varchar(50)
     )
   INSERT INTO [dbo].[Category]([Name])
   OUTPUT INSERTED.CategoryId, INSERTED.[Timestamp]
   INTO @Result(CategoryId, TimeStamp)
   VALUES (@Name)

   SElect * from @Result
END

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try this:

-- count use a temp table as well 
-- syntax: CREATE TABLE #t(CategoryId int,[Timestamp] datetime)
DECLARE @t table(CategoryId int,[Timestamp] datetime)

INSERT @t(CategoryId, [TimeStamp])
EXEC [dbo].[InsertCategory] @Name= @Name

SELECT CategoryId, [TimeStamp]
FROM @t

Upvotes: 5

Related Questions