April 24, 2008 at 12:55 pm
I have a stored procedure that has several parameters that are passed in that are used for different filter. The issue is they do not want me to use dynamic SQL. I have copied the Dynamic SQL code. Is there a way to do all this without Dymanic SQL?
IF (@MTDGreaterThan IS NOT NULL) AND (@MTDLessThan IS NULL) AND (@QTDGreaterThan IS NULL) AND (@QTDLessThan IS NULL)
SET @And = ' AND MonthToDate_Total >= ' + CONVERT(VARCHAR(20),@MTDGreaterThan) + ' '
ELSE IF (@MTDLessThan IS NOT NULL) AND (@MTDGreaterThan IS NULL) AND (@QTDGreaterThan IS NULL) AND (@QTDLessThan IS NULL)
SET @And = ' AND MonthToDate_Total <= ' + CONVERT(VARCHAR(20),@MTDLessThan) + ' '
ELSE IF (@MTDGreaterThan IS NOT NULL) AND (@MTDLessThan IS NOT NULL) AND (@QTDGreaterThan IS NULL) AND (@QTDLessThan IS NULL)
SET @And = ' AND MonthToDate_Total BETWEEN ' + CONVERT(VARCHAR(20),@MTDGreaterThan) + ' AND '+ CONVERT(VARCHAR(20),@MTDLessThan) + ' '
--ELSE IF (@MTDGreaterThan IS NULL) AND (@MTDLessThan IS NULL) AND (@QTDGreaterThan IS NULL) AND (@QTDLessThan IS NULL)
-- SET @And = NULL
ELSE IF (@QTDGreaterThan IS NOT NULL) AND (@QTDLessThan IS NULL) AND (@MTDGreaterThan IS NULL) AND (@MTDLessThan IS NULL)
SET @And = ' AND QuarterToDate_Total >= ' + CONVERT(VARCHAR(20),@QTDGreaterThan) + ' '
ELSE IF (@QTDLessThan IS NOT NULL) AND (@QTDGreaterThan IS NULL) AND (@MTDGreaterThan IS NULL) AND (@MTDLessThan IS NULL)
SET @And = ' AND QuarterToDate_Total <= ' + CONVERT(VARCHAR(20),@QTDLessThan) + ' '
ELSE IF (@QTDGreaterThan IS NOT NULL) AND (@QTDLessThan IS NOT NULL) AND (@MTDGreaterThan IS NULL) AND (@MTDLessThan IS NULL)
SET @And = ' AND QuarterToDate_Total BETWEEN ' + CONVERT(VARCHAR(20),@QTDGreaterThan) + ' AND '+ CONVERT(VARCHAR(20),@QTDLessThan) + ' '
-- ALL Level Filters
IF @LineFilter = ('Lines 1, 2, 3 and 4')
BEGIN
SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '
SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'0' ) + ' AND ' + CONVERT(Varchar(2),'4' )
END
-- Only Line 1 Filter
IF @LineFilter = ('Only Line 1')
BEGIN
SELECT * from #DLFiltered WHERE Active = 1
AND DownlineLevel = 1
Or (COnsultantID IS NULL)
END
-- Only Line 2 Filter
IF @LineFilter = ('Only Line 2')
BEGIN
SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '
SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'2' ) + ' AND ' + CONVERT(Varchar(2),'2' )
END
-- Only Line 3 Filter
-- Only Line 3 Filter
IF @LineFilter = ('Only Line 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '
SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Lines 1 and 2 Filter
IF @LineFilter = ('Lines 1 and 2')
BEGIN
SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '
SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'2' )
END
-- Lines 1, 2, and 3 Filter
IF @LineFilter = ('Lines 1, 2 and 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '
SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Lines 2 and 3
IF @LineFilter = ('Lines 2 and 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '
SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'2' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Lines 3 and 4 Filter
IF @LineFilter = ('Lines 3 and 4')
BEGIN
SET @SQLStr = 'SELECT * FROM #DlFiltered WHERE Active = 1 '
SET @SQLLevel = ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'4' )
END
-- All Active Filter
IF (@Status = 'All Active')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And
--Select @SQLstr
END
-- TIB Filter
IF (@Status = 'TIB')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
Set @SQLStr = @SQLstr + 'AND StatusID' +' = ''TIB'''
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And
END
-- Special TIB Filter
IF (@Status = 'Special TIB')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
Set @SQLStr = @SQLstr + 'AND StatusID' + ' = ''Special TIB'''
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And + ' '
END
-- All TIB Filter
IF (@Status = 'All TIB')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
Set @SQLStr = @SQLstr + 'AND StatusID' + ' = ''Special TIB''' + 'OR StatusID' + ' = ''TIB'''
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And + ' '
END
-- Grace Filter
IF (@Status = 'Grace')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
Set @SQLStr = @SQLstr + 'AND StatusID' +' = ''Grace'''
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And + ' '
END
-- New, Non Active Consultant Filter
IF (@Status = 'New, Non Active Consultant')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
Set @SQLStr = @SQLstr + 'AND StatusID' +' = ''New, Non Active Consultant'''
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And + ' '
END
-- In Reactivation 30 Day Filter
IF (@Status = 'In Reactivation 30 Day')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
Set @SQLStr = @SQLstr + 'AND StatusID' +' = ''In Reactivation 30 Day'''
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And + ' '
END
-- In Reactionation 1 Quarter Filter
IF (@Status = 'In Reactivation 1 Quarter')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
Set @SQLStr = @SQLstr + 'AND StatusID' +' = ''In Reactivation 1 Quarter'''
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And + ' '
END
-- All Reactivation Filter
IF (@Status = 'All in Reactivation')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
Set @SQLStr = @SQLstr + 'AND StatusID' + ' = ''In Reactivation 1 Quarter''' + ' OR StatusID' + ' = ''In Reactivation 30 Day'''
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And + ' '
END
-- Execute Dynamic SQL
--SELECT @SQLStr
EXEC sp_executesql @SQLstr;
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 24, 2008 at 6:56 pm
looks like you have a dynamic search condition. you may benefit from reading this:
http://sommarskog.se/dyn-search.html
---------------------------------------
elsasoft.org
April 25, 2008 at 5:58 am
There was a time when I was recommending using this code to eliminate dynamic SQL and still have a multi-valued search query:
SELECT....
FROM....
WHERE...
AND CASE WHEN @LocationTypeId IS NULL THEN 1
WHEN @LocationTypeId = a.CbiTypeId THEN 1
ELSE 0
END = 1
But we've run into some issues with this that throws the practice into question. But, it might be worth trying out. Caveat Emptor on this one though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2008 at 6:50 am
Since you're in 2005 - you might care to sprinkle a few "with recompile" statements in there, especially if you use conditionals in the WHERE clause like Grant is talking about. If the search criteria get complicated and would make for a widely different query, then WITH RECOMPILE will make sure you don't reuse an invalid exec plan.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 25, 2008 at 7:20 am
I would really appreciate a example. THe Shearch critertia is broke done into 3 section:
Criteria 1: Amount Filters
Criteria 2: Line Filters
Criteria 3: Status Filters
Each of these filters are used to return the recordset. There may be situations when Criteria 1 are NULLs but the other two will always have a value.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 25, 2008 at 7:27 am
I have got a small example at http://rajanjohn.blogspot.com/2008/04/dynamic-tsql-vs-case.html; see if that can help you anyway
April 25, 2008 at 7:47 am
I use this one often...
[font="Courier New"]...
...
WHERE (@A = AColumn OR @a IS NULL)
AND (@B = BColumn OR @b-2 IS NULL)
AND (@C = CColumn OR @C IS NULL)[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2008 at 7:56 am
One way to do this is liberal use of IsNull/Coalesce in the Where clause. For example:
Where MonthToDate_Total >= Coalesce(@MTDGreaterThan, -1) -- Assumes can't validly be negative
and MonthToDate_Total <= Coalesce(@MTDLessThan, 2147483647) -- Max value for Int data type
...
That way, if the variable is null, it measures against some static value that will always be true. Adjust the second values as needed.
In cases where you want an equality comparison, instead of greater than, etc., just put the column name as the second item in the Coalesce list.
Where Col1 = Coalesce(@Param, Col1)
If you have columns that might be null, that you want to compare, you have to modify it to something like:
Where Coalesce(Col1, 0) = Coalesce(@Param, Col1, 0)
Since null != null, Col1 != Col1 if Col1 is null. But 0 = 0, so it will still work. (This is one of the reasons to use Coalesce in this situation, instead of IsNull.)
All of these will, of course, result in index scans. There are no seeks in this method. But if you can't use dynamic SQL, it will at least work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2008 at 9:49 am
alorenzini (4/25/2008)
I would really appreciate a example.
there are lots of examples in the article I posted. Did you have a look?
---------------------------------------
elsasoft.org
April 25, 2008 at 10:07 am
Yes, I looked but they really didn't seem to fit what I was after. I understand the CASE statements but that not how the proc works.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 25, 2008 at 11:30 am
Try this:
CREATE PROCEDURE p_Get_Filtered (
@LineFilter varchar(50),
@StatusFilter varchar(50),
@MTDGreaterThan decimal(15,2),
@MTDLessThan decimal(15,2),
@QTDGreaterThan decimal(15,2),
@QTDLessThan decimal(15,2),
@AchieveLevelStart int,
@AchieveLevelEnd int)
SET NOCOUNT ON
DECLARE @LineTable table (Line int)
DECLARE @MTDGreater decimal(15,2),
@MTDLess decimal(15,2),
@QTDGreater decimal(15,2),
@QTDLess decimal(15,2)
--Code to build temp table #DLFiltered goes here
DELETE FROM #DLFiltered
WHERE Active <> 1
CREATE INDEX idx_CurrLvlMTDQTD ON #DLFiltered (CurrentLevelXID, MonthToDate_Total, QuarterToDate_Total)
SET @MTDGreater = CASE WHEN @MTDGreaterThan IS NOT NULL THEN @MTDGreaterThan ELSE -1.0 END
SET @MTDLess = CASE WHEN @MTDLessThan IS NOT NULL THEN @MTDLessThan ELSE 9999999999999.99 END
SET @QTDGreater = CASE WHEN @QTDGreaterThan IS NOT NULL THEN @QTDGreaterThan ELSE -1.0 END
SET @QTDLess = CASE WHEN @QTDLessThan IS NOT NULL THEN @QTDLessThan ELSE 9999999999999.99 END
-- ALL Level Filters
IF @LineFilter = 'Lines 1, 2, 3 and 4'
INSERT INTO @LineTable
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4
ELSE IF @LineFilter = 'Only Line 1'
INSERT INTO @LineTable
SELECT 1
ELSE IF @LineFilter = 'Only Line 2'
INSERT INTO @LineTable
SELECT 2
ELSE IF @LineFilter = 'Only Line 3'
INSERT INTO @LineTable
SELECT 3
ELSE IF @LineFilter = 'Lines 1 and 2'
INSERT INTO @LineTable
SELECT 1 UNION
SELECT 2
ELSE IF @LineFilter = 'Lines 1, 2 and 3'
INSERT INTO @LineTable
SELECT 1 UNION
SELECT 2 UNION
SELECT 3
ELSE IF @LineFilter = 'Lines 2 and 3'
INSERT INTO @LineTable
SELECT 2 UNION
SELECT 3
ELSE IF @LineFilter = 'Lines 3 and 4'
INSERT INTO @LineTable
SELECT 3 UNION
SELECT 4
ELSE-- If NO LineFilter is passed or it is null
INSERT INTO @LineTable
SELECT DISTINCT DownLineLevel
FROM #DlFiltered
ORDER BY DownLineLevel
IF @StatusFilter = 'All Active'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter = 'TIB'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE StatusId = 'TIB'
AND CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter = 'Special TIB'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE StatusId = 'Special TIB'
AND CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter = 'All TIB'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE StatusId IN ('TIB', 'Special TIB')
AND CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter = 'Grace'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE StatusId = 'Grace'
AND CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter = 'New, Non Active Consultant'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE StatusId = 'New, Non Active Consultant'
AND CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter = 'In Reactivation 30 Day'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE StatusId = 'In Reactivation 30 Day'
AND CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter = 'In Reactivation 1 Quarter'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE StatusId = 'In Reactivation 1 Quarter'
AND CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter = 'All in Reactivation'
SELECT *
FROM #DLFiltered d INNER JOIN @LineTable l ON d.DownLineLevel = l.Line
WHERE StatusId IN ('In Reactivation 1 Quarter', 'In Reactivation 30 Day')
AND CurrentLevelXID BETWEEN @AchieveLevelStart AND @AchieveLevelEnd
AND MonthToDate_Total BETWEEN @MTDGreater AND @MTDLess
AND QuarterToDate_Total BETWEEN @QTDGreater AND @QTDLess
ELSE IF @StatusFilter IS NULL AND @LineFilter = 'Only Line 1'
SELECT *
FROM #DLFiltered
AND DownlineLevel = 1
OR (COnsultantID IS NULL)
ELSE
SELECT *
FROM #DLFiltered
Dave Novak
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply