CREATE PROC uspProductSearch @ProductName VARCHAR(32) = NULL

AS

BEGIN

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = ' SELECT ProductID, ProductName=Name, Color, ListPrice ' + CHAR(10)+

' FROM Production.Product' + CHAR(10)+

' WHERE 1 = 1 ' + CHAR(10)

IF @ProductName IS NOT NULL

SELECT @SQL = @SQL + ' AND Name LIKE @pProductName'

PRINT @SQL

-- parametrized execution

EXEC sp_executesql @SQL, N'@pProductName varchar(32)', @ProductName

END

GO

-- Execute dynamic SQL stored procedure with parameter

EXEC uspProductSearch '%bike%'

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?