-----

--*****CW

--*****CW3.1 PROB 8*****

-- SQL Server dynamic sql stored procedure -- parametrized SQL statement -- Dynamic SQL is not allowed in function (UDF - User Defined Function) -----

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?

3.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?