-----

--*****CW3.1 PROB 7*****

---- SQL Server dynamic SQL variables - result into variable - input from variable -----

DECLARE @LastName nvarchar(32) = 'Smith', @MaxFirstName NVARCHAR(50) DECLARE @SQL NVARCHAR(MAX) = N'SELECT @pMaxFirstNameOUT = max(QUOTENAME(FirstName)) FROM Person.Person'+CHAR(13)+

'WHERE LastName = @pLastName'

PRINT @SQL+CHAR(13)

EXEC sp_executeSQL @SQL, -- getting variable input / setting variable output

N'@pLastName nvarchar(32),

@pMaxFirstNameOUT nvarchar(50) OUTPUT', -- parms definition

@pLastName = @LastName, -- input parameter

@pMaxFirstNameOUT=@MaxFirstName OUTPUT -- output parameter

SELECT [Max First Name] = @MaxFirstName, Legend='of last names ', LastName=@LastName

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?