TSQL Output Usage

The first part: The TSQL Output keyword has two application scenarios

1, as the parameter type of the stored procedure, returns data from the stored procedure

2, return the information in the rows affected by the INSERT, UPDATE, Delete, or merge statements, and you can capture the results of the output clauses embedded in the INSERT, UPDATE, Delete, or merge statements, and then insert the results into the target table.

3, grammar

<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}|$action

--$actiononly available for MERGE statements

Note: Output outputs the result to the client, and output into outputs the result to the specified table. In a single query, output and ouput into can appear at the same time, but occur at most once.

For more information, see: Https://msdn.microsoft.com/zh-cn/library/ms177564.aspx

Sample code

Create Tabledbo. Financemonth (Monthnumint, Quantityint); withCte as(    Select 1  asMonthnum, -  asQuantityUnion  All    SelectMonthnum+1, quantity+ -     fromCTEwhereMonthnum< A)Insert  intodbo. FinancemonthSelect * fromCte

Part II: Output as data type

1, create a stored procedure

ALTER PROCEDURE [dbo].[usp_test]    @RowCnt intOutput asBEGIN    SETNOCOUNT on; Select @RowCnt= Count(*)     fromdbo. FinancemonthEND

2, perform stored procedure

int@RowNum output@RowNum  

Part III: For returning results

1, the deleted data is returned to the temporary table, the removed data is temporarily stored in the deleted system table, the table is read-only, the scope is statement-level, and only exists in the DELETE statement.

if object_id('tempdb: #tempFiM') is  not NULLDrop Table#tempFiMSelect Top 0 * into#tempFiM fromdbo. FinancemonthDeletedbo. Financemonth output deleted.*  into#tempFiMwhereMonthnum<3Select *  from#tempFiM

2, the Insert data is returned to the temporary table, the added data is temporarily stored in the inserted system table, the table is read-only, the scope is statement-level, and only exists in the inserted statement.

if object_id('tempdb: #tempFiM') is  not NULLDrop Table#tempFiMSelect Top 0 * into#tempFiM fromdbo. FinancemonthInsert  intodbo. Financemonth output inserted.*  into#tempFiMSelect * fromdbo. FinancemonthwhereMonthnum<4Select *  from#tempFiM

The 3,update statement modifies the data, the modified data is stored in the temporary table deleted, the modified data is stored in the temporary table inserted, and the output statement is used to export the data from the system table inserted or deleted. An UPDATE statement, however, uses the output into clause up to one time.

if object_id('tempdb: #tempFiM') is  not NULLDrop Table#tempFiMSelect Top 0 * into#tempFiM fromdbo. FinancemonthUpdateDbo. FinancemonthSetQuantity=Quantity+1output deleted.*  into#tempFiMoutput inserted.* whereMonthnum<4Select *  from#tempFiM

TSQL Output Usage