SET NOCOUNT ON;

USE AdventureWorks2012;

DECLARE @s AS NVARCHAR(100);

SET @s = N'Miller'; -- originates in user input -- escape sequence N

DECLARE @sql AS NVARCHAR (500); -- 100

SET @sql = 'SELECT p.BusinessEntityID, p.FirstName, p.LastName, p.PersonType, e.JobTitle from Person.Person p

INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID WHERE p.LastName = N''' + @s + N''';';

PRINT @sql; -- for debug purposes

--EXEC (@sql);

EXEC sp_executesql @sql;

-- OR CAN ALSO BE WRITTEN AS BELOW --

SET NOCOUNT ON;

USE AdventureWorks2012;

DECLARE @s AS NVARCHAR(100);

SET @s = N'Miller'; -- originates in user input

DECLARE @sql AS NVARCHAR (500);

SET @sql = 'SELECT p.BusinessEntityID, p.FirstName, p.LastName, p.PersonType, e.JobTitle from Person.Person p

INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID WHERE p.LastName = @lastname;';

PRINT @sql; -- for debug purposes

EXEC sp_executesql

@stmt = @sql,

@params = N'@lastname AS NVARCHAR(100)',

@lastname = @s;

-- how it handles sp_executesql in the system side?

1. What kind of dynamic SQL it is? (such as passing input / output parameters or concatenating the user inputs, etc.)

The first dynamic SQL is concatenating the user inputs. The second one is passing values through input parameters (@stmt, @params, @lastname variables)

2. Explain the problem?

In the second dynamic sql, @sql is assigned the select statement string. Select statement takes input parameter @lastname value and check it for employees lastname and publish their id, lastname, firstname persontype and jobtitle by joining employee and person table.

Write dynamic sql query to show ID, firstname, lastname, persontype and jobtitle of Person whose lastname is supplied through an input parameter. Use default value for lastname as Miller.

3. Is this dynamic sql efficient or not? Why?

Second dynamic sql is more efficient than the first one. Reason being the security. Concatenation of user input is prone to SQL injection.