-----

use AdventureWorks2012;

--*****CW3.1 PROB 6*****

-- sp_executeSQL usage with input and output parameters

-- *******

-- Parameterized dynamic SQL has security, performance and other benefits

-- *******

-- Always use parameterized dynamic SQL if possible

-- *******

DECLARE @SQL NVARCHAR(max), @ParmDefinition NVARCHAR(1024)

DECLARE @ListPrice money = 2000.0, @LastProduct varchar(64)

SET @SQL = N'SELECT @pLastProduct = max(Name)

FROM Production.Product

WHERE ListPrice >= @pListPrice'

SET @ParmDefinition = N'@pListPrice money, @pLastProduct varchar(64) OUTPUT'

EXECUTE sp_executeSQL -- Dynamic T-SQL

@SQL,

@ParmDefinition,

@pListPrice = @ListPrice,

@pLastProduct=@LastProduct OUTPUT

SELECT [ListPrice >=]=@ListPrice, LastProduct=@LastProduct

1. What kind of dynamic SQL it is? (such as passing input / output parameters or concatenating the user inputs, etc.)
2. Explain the problem?
3. Is this dynamic sql efficient or not? Why?