July 15, 2008 at 9:57 am
I have the following code:
DEclare @Level10bit
Set @Level10 = 1
IF (@Status = 'All Active')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' +'@Level10' + '='+CONVERT(Varchar(2),'1' )
SET @SQlstr = @SQLStr + ' ' + @SQlLevel + ' ' + @And+ ' ' +@OrStr
Select @SQLstr
END
I get the following error:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Level10".
Msg 208, Level 16, State 0, Line 603
I know it has something to do with how I have the +'@Level10' + written
Please advise
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
July 15, 2008 at 10:02 am
You declare @Level10 in the code that creates the dynamic SQL, but not in the dynamic SQL string. If you want to use it in the SQL string, you should either declare and assign in there, or use the value in the SQL string, not the name.
What you probably want is:
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID = ' + CONVERT(Varchar(2),'10' ) + ' AND ' + cast(@Level10 as char(1)) + '='+CONVERT(Varchar(2),'1' )
- 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
July 15, 2008 at 10:13 am
I tried the solution you provided and this is the string it created.
SELECT * FROM #DLFiltered WHERE Active = 1 AND CurrentLevelXID = 10 AND 1=1 AND DownlineLevel BETWEEN 1 AND 3 AND MonthToDate_Total BETWEEN 0 AND 2000 OR (ConsultantID IS NULL)
I need it to create the following string:
SELECT * FROM #DLFiltered WHERE Active = 1 AND CurrentLevelXID = 10 AND @Level10=1 AND DownlineLevel BETWEEN 1 AND 3 AND MonthToDate_Total BETWEEN 0 AND 2000 OR (ConsultantID IS NULL)
I have @Level10 defined outside the string and need to bring it in the string. It is actually a parameter that is passed into the proc,
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
July 15, 2008 at 10:52 am
Then you need to add a declaration for @Level10 to the string, and an assignment.
Something like:
set @SQL = 'declare @Level10 bit; set @Level10 = ' + cast(@Level10 as char(1)) + ';'
Do that before you add other stuff to the string.
Both solutions will actually do the same thing to the final result.
If, for example, you used my first solution, and @Level10 were set to 0 in the calling code, you would end up with "and 0 = 1" in the string, which would accomplish the same thing. Unless, of course, @Level10 will always be 1, in which case it doesn't need to be a variable and can be left out of the whole thing.
- 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
July 15, 2008 at 11:31 am
The 1=0 works great. Thanks
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 1, 2008 at 11:31 am
The problem now is the point of the 1=1 and 0=1 is that I do not want to display the level that have 0=1 but right now it is still pulling all levels.
This is the code that creates the string:
-- 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
And this is the SQL statement that is created:
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)
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 1, 2008 at 12:29 pm
alorenzini (8/1/2008)
The problem now is the point of the 1=1 and 0=1 is that I do not want to display the level that have 0=1 but right now it is still pulling all levels.This is the code that creates the string:
-- 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
And this is the SQL statement that is created:
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)
As long as you have this AND 1=1 in the criteria - all rows are going to satisfy the request because 1 will always equal 1.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 1, 2008 at 12:36 pm
I know the logic is sound but the issue is that in the case of the following result String:
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)
When I execute the string with
EXEC sp_executesql @SQLstr;
I do not expect to see in this case level30, level40 or Level50 because they are set to 0=1 but now its returning all the levels regardless.
Perhaps it's the implementation.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 1, 2008 at 12:55 pm
alorenzini (8/1/2008)
I know the logic is sound but the issue is that in the case of the following result String: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)
When I execute the string with
EXEC sp_executesql @SQLstr;
I do not expect to see in this case level30, level40 or Level50 because they are set to 0=1 but now its returning all the levels regardless.
Perhaps it's the implementation.
Well, the reason you are getting those rows is because that is what you are asking for. Let's take a look at this by reformatting it a little:
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)
Now, looking at it we can clearly see the criteria where you are looking for CurrentLevelXID will always be true. It will always be true because you have AND 1=1 following the check for CurrentLevelXID = 10.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 1, 2008 at 1:01 pm
I guess the next question is how do I fix it. How would I tell it that I need to omit the 0=1 levels.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 1, 2008 at 1:14 pm
Actually, it is quite simple - all you need to do is look at what you have.
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)
The bolded section should be paired - based upon what I can see. I am really not sure why you need dynamic SQL anyways, but that should fix this issue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 1, 2008 at 1:23 pm
OK those statements look the same to me. But I was thinking I should be able to filter them out in my temp table before I even get to the dynamic SQL: This is the select statement that fills the temp table:
Select
'OrigConsID' = @ConsultantID
,(Select Top 1 FirstName + ' ' + LastName FROM Consultant.uvw_ConsultantDownline d
WHERE D.ConsultantID = @ConsultantID) AS 'OrigConsName'
,D.ConsultantID
,D.Downlinelevel
,D.ConsultantName
,D.EffectiveDate
,ISNULL(Convert(Char(10),D.BumpupDate,101),'') as BumpupDate
,D.ConsultantXID
,D.CurrentLevelXID
,D.AchieveTitle
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,ISNULL(v.QtrlyPORSalesLessCredits, 0) AS QuarterToDate_total
,ISNULL(v.MthlyPORSalesLessCredits, 0) AS MonthToDate_Total
,ISNULL(v.Mthly1stLinePORSales, 0) AS Mthly1stLinePORSales
,ISNULL(v.Qtrly1stLinePORSales,0) AS Qtrly1stLinePORSales
,ISNULL(v.Mthly2ndLinePORSales,0) AS Mthly2ndLinePORSales
,ISNULL(v.Qtrly2ndLinePORSales,0) AS Qtrly2ndLinePORSales
,ISNULL(v.Mthly3rdLinePORSales,0) AS Mthly3rdLinePORSales
,ISNULL(v.Qtrly3rdLinePORSales,0) AS Qtrly3rdLinePORSales
,CONVERT(Char(10),Max(u.OrderCreateDate),101) AS 'LastOrderDate'
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.BilltoRegionXID
,D.HomePhone
,D.BusinessPhone
,D.Fax
,D.OtherPhone
,D.EmailAddress
,ISNULL(r.RepFlag,' ')AS RepFlag
,D.SponsorXID
,D.Active
,D.StatusID
,D.StatusIDName
,Convert(varchar(10),D.NACDate,101) AS AgreementDate
from #Downline D
--LEFT OUTER JOIN #Orders O ON D.ConsultantID = O.ConsultantID
inner JOIN volume.volume V ON D.ConsultantID = V.ConsultantID --AND v.Enddate BETWEEN @MonthStartDt AND @MonthEndDt
LEFT OUTER JOIN uvw_DownlineOrder U ON D.ConsultantID = u.ConsultantID collate Latin1_General_CI_AS
LEFT Outer JOIN #Temp2 r ON d.ConsultantID = r.ConsultantID collate Latin1_General_CI_AS AND r.repflag = 'X'
WHERE (D.Active = 1)AND (BillToState = @State) OR (@State IS NULL) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND ((d.CurrentLevelXID=10 AND @level10 =1) OR (d.CurrentLevelXID = 20 AND @level20=1) OR (d.CurrentLEVELXID=30 AND @Level30 =1))-- OR @level40 <>0 OR @Level50 <>0 OR @level60 <> 0 OR @Level70 <>0 OR @level80<> 0)
I tried added the level logic to the where claus but it still doing the same thing. What am I missing?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 1, 2008 at 1:49 pm
Art, all you needed to do was add parantheses around the bolded section to 'pair' those two criteria. That should fix the issue you have with that dynamic query.
BTW - with the way you are building the query, I see no reason at all to use dynamic SQL. I would rewrite it without it and just use the variables in the query as needed.
One other note, if you take a look at how I formatted the query - you should see that it is a lot easier to see how things are grouped and organized. You really should start formatting your queries (even if built dynamically) so you can see how they are organized. It will help you identify these kinds of problems a lot faster.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 1, 2008 at 2:06 pm
Ok let eliminate the dynamic SQL. How would I introduce the @Level into the WHERE clause?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 1, 2008 at 2:50 pm
alorenzini (8/1/2008)
Ok let eliminate the dynamic SQL. How would I introduce the @Level into the WHERE clause?
IF (@Status = 'All active')
BEGIN
SELECT *
FROM #DLFiltered
WHERE Active = 1
AND ((CurrentLevelXID = 10 AND @Level10 = 1)
OR (CurrentLevelXID = 20 AND @Level20 = 1)
OR (CurrentLevelXID = 30 AND @Level30 = 1)
OR (CurrentLevelXID = 40 AND @Level40 = 1)
OR (CurrentLevelXID = 50 AND @Level50 = 1)
OR (CurrentLevelXID = 60 AND @Level60 = 1)
OR (CurrentLevelXID = 70 AND @Level70 = 1)
OR (CurrentLevelXID = 80 AND @Level80 = 1))
AND MonthToDate_Total BETWEEN 0 AND 2000
OR ConsultantID IS NULL
END;
Now, I am not sure what you are doing to include the MonthToDate_Total and the ConsultantID - but, unless you are building that differently based upon different input I don't see any reason to even use a variable. But again, I don't know what you are trying to get to.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply