How to access inserted and deleted logical tables with a stored procedure

Question

Is there a way to access inserted and deleted tables using a stored procedure or a function WITHOUT using or passing values from a trigger.

Ex:

INSERT INTO dbo.Table (ID) 
VALUES (1)

SELECT * FROM inserted   -> This raises an error

Is there a way to do something like this in order to capture the inserted value?

I was able to do this to a query which uses the MERGE command as below.

MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID)  

WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 
                 OR TARGET.Rate <> SOURCE.Rate 
   THEN 
      UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
                 TARGET.Rate = SOURCE.Rate 

WHEN NOT MATCHED BY TARGET 
   THEN 
      INSERT (ProductID, ProductName, Rate) 
      VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

WHEN NOT MATCHED BY SOURCE 
   THEN 
      DELETE

OUTPUT 
    $action, 
    DELETED.ProductID AS TargetProductID, 
    DELETED.ProductName AS TargetProductName, 
    DELETED.Rate AS TargetRate, 
    INSERTED.ProductID AS SourceProductID, 
    INSERTED.ProductName AS SourceProductName, 
    INSERTED.Rate AS SourceRate;

Is there a way to access inserted and deleted tables for simple SQL queries?

[You can still use output](https://stackoverflow.com/questions/16147067/output-from-insert-into-stored-procedure). You just need to change your syntax a bit :) [More examples in the docs](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017) — S3S, Jan 07 '19 at 15:32
Are you trying to: (1) use a stored procedure in a trigger and pass `inserted` and `deleted` to it as implied by the title or (B) trying to capture the changes made by a statement for subsequent processing without using a trigger? The first case can be handled by copying the contents of the pseudotables into temporary tables and passing them to the SP. The second can be handled using an `output` clause (which provides access to both _before_ and _after_ values in the case of an `update`). — HABO, Jan 07 '19 at 17:28

Zohar Peled · Accepted Answer · 2019-01-07T15:50:12.377

You can use the inserted and deleted tables only in triggers or in the output clause of DML statements.

In an insert statement, the output clause can reference the inserted table:

DECLARE @Ids AS TABLE (id int);

INSERT INTO dbo.Table (ID) 
OUTPUT Inserted.ID INTO @Ids(id)
VALUES (1), (2), (3);

In an update statement, you can reference both the inserted table and the deleted table:

DECLARE @Ids AS TABLE (oldId int, newId int);

UPDATE dbo.Table 
SET ID = 1
OUTPUT Deleted.ID, Inserted.ID INTO @Ids(oldId, newId);

And in a delete statement, you can reference the deleted table:

DECLARE @Ids AS TABLE (id int);

DELETE dbo.Table 
OUTPUT Inserted.ID INTO @Ids(id)
FROM dbo.Table 
WHERE ID IN (1, 2, 3);

A Merge statement is unique since you have access to both the source table and the inserted/deleted tables in it's output clause, as demonstrated on this post.

How to access inserted and deleted logical tables with a stored procedure

1 Answers1