CODEWITHSUNDEEP
c#sqlprocedure
Nityuiop18
Nityuiop18

Reputation: 113

SQL procedure using OUTPUT clause doesn't return value

I have built a procedure which should insert a new row into a table and return its id.

CREATE PROCEDURE [InsertFood]
AS 
 DECLARE @ID TABLE (foodId INT)
 insert into TblFood
 OUTPUT INSERTED.foodId INTO @ID(foodId) 
 default values 
 select * from @ID
 return

When I run this procedure it always returns 0.

Upvotes: 0

Views: 253

Answers (1)

Pranay Rana
Pranay Rana

Reputation: 176956

REad this MSDN post provide example : Retrieving Identity or Autonumber Values

Example :

CREATE PROCEDURE dbo.InsertCategory
  @CategoryName nvarchar(15),
  @Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()

CREATE PROCEDURE [InsertFood]
AS 
 DECLARE @ID int 
 --insert data in table Example 
  ----INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
  ----VALUES ('Damaged Goods', 5, 2.5, GETDATE()); 
 --insert data in table 
 select @ID =  SCOPE_IDENTITY()
 return @ID

You can use Scope_Identity() to get the last value.

Upvotes: 1

Related Questions