August 1, 2008 at 11:12 am
I am trying to generate a dynamic SQL string based on the following:
DECLARE@ConsultantIDnVarChar(50)
DECLARE@AchieveLevelStartint
DECLARE@AchieveLevelEndint
DEclare @Level10bit
Declare @Level20bit
Declare @Level30bit
Declare @Level40bit
Declare @Level50bit
DECLARE@SQLStrNVARCHAR(4000)
,@SQLLevelNVARCHAR(4000)
,@PeriodDateDateTime
,@ORStrNvarchar(50)
,@StateSQLnvarchar(50)
Set @Level10 = 1
Set @Level20 = 1
Set @Level30 = 0
Set @Level40 = 0
Set @Level50 = 0
There is some table manipulation below this but when I want to generate the final Dynamic
This is the clause that puts it all together.
SELECT @SQLStr
-- All Active Filter
IF (@Status = 'All active')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
SET @SQLStr = @SQLStr + ' '+ @SQlLevel + ' AND (CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' ) + ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'20' ) + ' AND ' + cast(@Level20 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'30' ) + ' AND ' + cast(@Level30 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'40' ) + ' AND ' + cast(@Level40 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'50' ) + ' AND ' + cast(@Level50 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'60' ) + ' AND ' + cast(@Level60 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'70' ) + ' AND ' + cast(@Level70 as char(1)) + '='+CONVERT(Varchar(2),'1' )+')'+ ' OR (CurrentLevelXID = ' + CONVERT(Varchar(2),'80' ) + ' AND ' + cast(@Level80 as char(1)) + '='+CONVERT(Varchar(2),'1' )+'))'
SET @SQlstr = @SQLStr + ' ' + @And+ ' ' +@OrStr
SELECT @SQLstr
END
This is the string that is generated.
SELECT * FROM #DLFiltered WHERE Active = 1 AND DownlineLevel BETWEEN 1 AND 3 OR (DownlineLevel = 0) AND (CurrentLevelXID = 10 AND 1=1 OR (CurrentLevelXID = 20 AND 1=1) OR (CurrentLevelXID = 30 AND 0=1) OR (CurrentLevelXID = 40 AND 0=1) OR (CurrentLevelXID = 50 AND 0=1) OR (CurrentLevelXID = 60 AND 1=1) OR (CurrentLevelXID = 70 AND 1=1) OR (CurrentLevelXID = 80 AND 1=1)) AND MonthToDate_Total BETWEEN 0 AND 2000 OR (ConsultantID IS NULL)
But the problem is when it executes it still returning all the CurrentLevelXID and not just the ones the @LEVEL = 1 if the @Level = 0 they should be omitted.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 28, 2008 at 6:10 am
I suggest you read these two articles on dynamic SQL by Erland Sommarskog:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
In fact, I think you should read all of Erland's articles, but you should at least start with these two.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
August 28, 2008 at 6:46 am
It almost sounds like the query it self is not working the way you want it to work. Anyway the few times I have to use dynamic SQL (very few) I tend to do it one row at a time to make sure it works as expected.
The more you are prepared, the less you need it.
August 28, 2008 at 8:16 am
CurrentLevelXID is of the varchar type. You have to use single quotes in WHERE condition. Otherwise, something like (CurrentLevelXID = 70 AND 1=0) will return TRUE.
Try to debug the SQL expression you received as a result of dynamic SQL to receive expected results and then edit your dynamic SQL accordingly.
August 28, 2008 at 8:30 am
Glen (8/28/2008)
CurrentLevelXID is of the varchar type. You have to use single quotes in WHERE condition. Otherwise, something like (CurrentLevelXID = 70 AND 1=0) will return TRUE.Try to debug the SQL expression you received as a result of dynamic SQL to receive expected results and then edit your dynamic SQL accordingly.
Khm... Can you provide an example?
You're implying that 1 can under certain circumstances be equal to 0. I'm sure I'm not the only one who would like to know what those circumstances are. 🙂
Just in case:
http://msdn.microsoft.com/en-us/library/ms188372.aspx
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
August 28, 2008 at 8:52 am
I adore sarcasm, but I was hoping that the example I provided was clear?
Of course I might be wrong, but what I meant was:
I understood that you are using this construction (CurrentLevelXID = 70 AND 1=0) to filter the value of CurrentLevelXID = 70?
If that is correct and CurrentLevelXID is varchar data type, then
(CurrentLevelXID = 70 AND 1=0) will always return FALSE, and the value of '70' for CurrentLevelXID will appear in your resultset (TRUE)?
August 29, 2008 at 8:06 am
When I build dynamic SQL, I always start by writing it out by hand and formatting it properly to make sure it is exactly what I want. Only then do I try to convert it into dynamic code. Looking at what your code produces and reformatting it, I am pretty sure that it is not what you were wanting. I believe that your OR's are messing up your logic. Nothing else matters if either Downlinelevel is zero or ConsultantID is null. Those OR's will win. If neither one of those is the case, then all your outer AND's will need to be true, but you still may have a problem with the second AND. Without appropriate parentheses to control your boolean logic you can get very bad results.
SELECT *
FROM #DLFiltered
WHERE Active = 1
AND DownlineLevel BETWEEN 1 AND 3
OR (DownlineLevel = 0)
AND (CurrentLevelXID = 10
AND 1=1
OR (CurrentLevelXID = 20 AND 1=1)
OR (CurrentLevelXID = 30 AND 0=1)
OR (CurrentLevelXID = 40 AND 0=1)
OR (CurrentLevelXID = 50 AND 0=1)
OR (CurrentLevelXID = 60 AND 1=1)
OR (CurrentLevelXID = 70 AND 1=1)
OR (CurrentLevelXID = 80 AND 1=1)
)
AND MonthToDate_Total BETWEEN 0 AND 2000
OR (ConsultantID IS NULL)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply