Summary output usage

First usage

Returns the information of each row affected by the insert, update, or delete statement, or returns the expression based on the preceding lines. These results can be returned to the processing application,

For use in validation messages, archiving, and other similar application requirements. You can also insert results into tables or table variables.

Used:

Delete

Insert

Update

Syntax:

<Output_clause >::=
{
[Output <dml_select_list> into {@ table_variable | output_table} [(column_list)]
[Output <dml_select_list>]
}
<Dml_select_list >::=
{<Column_name> | scalar_expression} [[as] column_alias_identifier]
[,... N]

<Column_name >::=
{Deleted | inserted | from_table_name}. {* | column_name}

 

-- Create a table first
Create Table Test2 (
[No] int,
N nvarchar (100)
)
-- Insert Test Data
Insert into Test2 ([No], n)
Select 1, 'A'
Union
Select 2, 'B'
Union
Select 3, 'C'
Go

-- Called when inserting data
-- Display the inserted data
Insert Test2 ([No], n)
Output inserted .*
Values (4, n'd ');
Go

Select * From Test2
Go

 

Declare @ mytablevar table (
[No] int,
N nvarchar (100)
);

-- Delete the record with [No] = 1 and save the deletion result to the table variable @ mytablevar.
Delete from Test2
Output deleted .*
Into @ mytablevar
Where [No] = 1

Select * From @ mytablevar

Select * From Test2

 

Declare @ mytablevar table (
Newno int,
Newn nvarchar (100 ),
Oldno int,
Oldn nvarchar (100)
);

-- Modify the record and save the record before and after modification to the table variable
Update Test2
Set n = 'ccc'
Output
Inserted. No,
Inserted. N,
Deleted. No,
Deleted. n
Into @ mytablevar
Where [No] = 3

Select * From @ mytablevar

Select * From Test2

Second usage

Declare @ largestmonth nvarchar (10)

Declare @ strsql nvarchar (1000)
Set @ strsql = "select @ largestmonth = convert (nvarchar (10), max ([time]), 120 )"
Set @ strsql [email protected] + "from tablename"
 
Set @ strsql [email protected] + @ Filter
Exec sp_executesql @ strsql, n' @ largestmonth nvarchar (10) output', @ largestmonth output

Third usage

Create procedure sp_output
@ Output int output
As
Set @ output = 121
Return 1

Declare @ out int
Declare @ count int
Exec @ COUNT = sp_output @ out output
Select @ count
Select @ out

Summary output usage