-- Dynamic SQL stored procedure - Dynamic SQL IN list clause

--*****CW3.1 PROB 5*****

/* WARNING - This following stored proc is vulnerable to SQL Injection Attack */

-- JOIN would be a preferred solution

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?