DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to design a stored procedure with dynamic SQL filtering?

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

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE