CREATE PROCEDURE CustomerListByState @States VARCHAR(128)

AS

BEGIN

DECLARE @SQL NVARCHAR(1024)

SET @SQL = 'select CustomerID, CompanyName, ContactName, Phone, Region from Customers where Region IN (' + @States + ')' + ' order by Region'

PRINT @SQL -- For testing and debugging /* The following query is executed as dynamic SQL select CustomerID, CompanyName, ContactName, Phone, Region from Customers where Region IN ('WA', 'OR', 'ID', 'CA') order by Region */ -- Dynamic SQL execution

EXEC Sp_executesql @SQL

END

GO

-- Execute dynamic SQL stored procedure

DECLARE @States VARCHAR(100)

SET @States = '''WA'', ''OR'', ''ID'', ''CA'''

EXEC CustomerListByState @States

GO

(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?