Dynamic TSQL Gets the return value

Using execute and sp_executesql to implement dynamic TSQL, how to get the return value of dynamic TSQL?

Part I.: Execute

1,execute is a function that executes an SQL statement, and the example statement is as follows

Declare @sql nvarchar(Max)Declare @return_cnt intif object_id('tempdb: #tempcnt') is  not NULLDrop Table#tempcnt
Create Table#tempcnt (CNTint)Set @sql=N'INSERT INTO #tempcnt select COUNT (*) from sys.objects'exec(@sql)Select *  from#tempcnt

As you can see from the sample code, the staging table is declared before the dynamic SQL scope, but it can be used in dynamic SQL, which indicates that the scope of the staging table is connection-oriented and that the temporary table can be accessed as long as the current link is present.

Part II: sp_executesql

1,sp_executesql has the ability to get the return value

Declare @sql nvarchar(Max)Declare @return_cnt intSet @sql=N'Select @cnt = count (*) from sys.objects'execsp_executesql@sqlN'@cnt int Output',@return_cntOutputPrint @return_cnt

Dynamic TSQL Gets the return value