Reading INSERT Output from a Stored Procedure
I have the 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 is the category ID of the inserted category (it is outputted in the insert statement). It writes to the output, but I can't figure out how to assign it to a variable without modifying the stored procedure. In C #, I can use command.ExecuteReader and I get it, but I don’t know how to get it on SQL Server.
I also cannot use SCOPE_IDENTITY as we have our own ID generation system.
+3
source to share
2 answers
Try the following:
-- 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
+4
source to share
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
+1
source to share