Get dynamic SQL query statement return value (sp_executesql)

When writing stored procedures, you often encounter the need to splice SQL statements, generally only to perform the post-stitching statements using EXEC (@sql).

Today, a stored procedure needs to get the results of a dynamic SQL query.

Requirement Description: The cost value is queried in a table based on the ID value (table name is indeterminate but the table structure is determined, as in the following product table)

If we do not consider getting the return value, we can write this:

  Declare @tableName varchar( -)  Declare @id varchar(Ten)  Declare @costNumeric -,2)  Declare @sql nvarchar( $)    Set @tableName='Product'  Set @id='1'  Set @sql='Select cost from'+@tableName+'where id='+@id  exec(@sql)

The first attempt to get the return value is the following two methods:

Set @sql = '  '+@tableName+'  where id='+  @id-- Error Method 1　　

Set @cost = (exec(@sql))    -- Error Method 2

Both of the above methods will be error, to help the almighty network to find a lovely function--sp_executesql can meet our requirements:

  Set @sql = '  '+@tableName+'  where [email protected]'   exec@sql, N'@cost Numeric (18,2) out, @id varchar  @cost out,@id

Not only can get the return value, but also can pass the parameter has no! Unfortunately, the table name still needs to be stitched into the SQL statement.

Note: The type of @sql needs to be one of the three types of ' Ntext/nchar/nvarchar '.

sp_executesql Introduction and use of the source of the garden friends in a more detailed introduction to this function.

Get dynamic SQL query statement return value (sp_executesql)