|
Execute the following
Microsoft SQL Server 2008 T-SQL database scripts in SSMS Query Editor to demonstrate the use of dynamic SQL to construct a stored procedure (sproc) with dynamic filtering for web page support. Based on internet user choices (or lack of choices), specialized query is built in a string and dynamically executed.
USE AdventureWorks2008;
GO
CREATE /* ALTER */ PROCEDURE procWebProductSearch
(
@ProductSubcategoryID INT,
@Color NVARCHAR(15),
@ListPrice INT
)
AS
SET NOCOUNT ON
DECLARE @SQLCommand NVARCHAR(MAX)
SET @SQLCommand = ' SELECT ProductName=Name, * FROM Production.Product '
-- Check if WHERE clause filtering needed
IF @ProductSubcategoryID IS NOT NULL
OR @Color IS NOT NULL
OR @ListPrice IS NOT NULL
BEGIN
SET @SQLCommand = @SQLCommand + ' WHERE '
END
IF @ProductSubcategoryID IS NOT NULL BEGIN
SET @SQLCommand = @SQLCommand + ' ProductSubcategoryID = '
+ convert(varchar,@ProductSubcategoryID) + ' '
END
IF @Color IS NOT NULL BEGIN
IF @ProductSubcategoryID IS NOT NULL
SET @SQLCommand = @SQLCommand + ' AND '
SET @SQLCommand = @SQLCommand + ' Color LIKE ''' +
@Color + '%'''
END
IF @ListPrice IS NOT NULL BEGIN
IF @ProductSubcategoryID IS NOT NULL
OR @Color IS NOT NULL
SET @SQLCommand = @SQLCommand + ' AND '
SET @SQLCommand = @SQLCommand + ' ListPrice <= ' +
CAST(@ListPrice AS VARCHAR)
END
-- Final sort
SET @SQLCommand = @SQLCommand + ' ORDER BY ProductName '
-- Debugging statement
PRINT @SQLCommand
-- Execute the dynamically constructed SQL query
EXEC sp_executeSQL @SQLCommand
GO
-- Test scripts
-- Web site user selects Subcategory only from a drop-down menu
-- ProductSubcategoryID 20 is Full-Finger Gloves, L
-- Tab over to Messages to see the dynamically created SQL queries
EXEC procWebProductSearch 20, null, null
GO
-- Web site user also specifies highest price
EXEC procWebProductSearch 20, null, 30
GO
-- Color is also specified by radio-button selection on the website
EXEC procWebProductSearch 20, 'Red', 30
GO
EXEC procWebProductSearch 20, 'Black', 30
GO
|
