CODEWITHSUNDEEP
c#sqldatabase
zhrgci
zhrgci

Reputation: 676

Must declare scalar variable '@ID' with OUTPUT INTO

I get this error, but I thought i declared it already. Can someone tell me where I'm going wrong?

DECLARE @ID TABLE(OrderID INT)

INSERT INTO [dbo].[Order](UserID, To_Adress, OrderDate, TravelTime, ItemCount, Status, TotalPrice) 
OUTPUT INSERTED.OrderID INTO @ID
VALUES (1, 'BIKINI BOTTOM', '20191030 15:00:00', '20191030 15:35:00', 1, 'InTheMaking', 7.50) 

INSERT INTO [dbo].[Order_Product](OrderID, ProductID) VALUES (@ID, 12)

Background: So basically I tried to get the auto-incremented ID that I just inserted and tried to implement the DECLARE OUTPUT method instead of the SCOPE_IDENTITY()-way from the comment from BugFinder. Can I get the ID of the data that I just inserted?

Upvotes: 1

Views: 532

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Yes, use a FROM clause, because the value is in a table:

DECLARE @IDS TABLE (OrderID INT);

INSERT INTO [dbo].[Order](UserID, To_Adress, OrderDate, TravelTime, ItemCount, Status, TotalPrice) 
    OUTPUT INSERTED.OrderID INTO @IDS
    VALUES (1, 'BIKINI BOTTOM', '20191030 15:00:00', '20191030 15:35:00', 1, 'InTheMaking', 7.50);

INSERT INTO [dbo].[Order_Product](OrderID, ProductID) 
    SELECT i.OrderID, 12
    FROM @IDS i;

I changed the name to @IDS to make it clear that it can have more than one row.

Upvotes: 4

Related Questions