January 22, 2008 at 9:17 am
I have the following code:
If I use the 'All Active' @status it works fine but if I use and of the other @status option (See filter below) it does not return anything not even the @SQLstr string in the Select statement after the filter statements,even though there is a valid at least one valid record for each of the different @status.:crazy:
DECLARE@AchieveLevelStart int
DECLARE@AchieveLevelEndint
DECLARE@LineStart varchar (10)
DECLARE @LineEndvarchar(10)
DECLARE@status varchar(10)
DECLARE@GreaterThan Decimal
DECLARE@LessThanDecimal
DECLARE @AndVARCHAR(200)
,@SQLStrNVARCHAR(4000)
,@PeriodDateDateTime
SET @ConsultantID = @ConsultantID
SET @PeriodDate = GETDATE()
Set @ConsultantID = '0000006'
SET @AchieveLevelStart = 10
SET @AchieveLevelEnd = 80
SET @LineStart = '1'
SET @LineEnd = '4'
SET @status = 'New, Non Active Consultant'
SET @GreaterThan = 0
SET @LessThan = 20000
-- All Active Filter
IF (@Status = 'All Active')
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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
-- TIB Filter
IF (@Status = 'TIB')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
-- Special TIB Filter
IF (@Status = 'Special TIB')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
-- Special Grace Filter
IF (@Status = 'Grace')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
SELECT @SQLstr
-- New Filter
IF (@Status = 'New, Non Active Consultant')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
SELECT @SQLstr
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 22, 2008 at 10:08 am
Art
You need to assign a value to the varchar variable @AND. Until you do, the value is NULL. Then read the following section in BOL: [SET CONCAT_NULL_YIELDS_NULL].
You also need to place each conditional code block within a begin/end.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 22, 2008 at 10:15 am
@And is being assigned above the filter block;
IF (@GreaterThan IS NOT NULL) AND (@LessThan IS NULL)
SELECT @And = ' AND MonthToDate_Total >= ' + CONVERT(VARCHAR(20),@GreaterThan) + ' '
ELSE IF (@LessThan IS NOT NULL) AND (@GreaterThan IS NULL)
SELECT @And = ' AND MonthToDate_Total <= ' + CONVERT(VARCHAR(20),@LessThan) + ' '
ELSE IF (@GreaterThan IS NOT NULL) AND (@LessThan IS NOT NULL)
SELECT @And = ' AND MonthToDate_Total BETWEEN ' + CONVERT(VARCHAR(20),@GreaterThan) + ' AND '+ CONVERT(VARCHAR(20),@LessThan) + ' '
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 22, 2008 at 10:31 am
ok.
Looks to me like the 2nd/3rd and 4th filter blocks are exactly the same - is this correct?
It helps to see all of the code - I can see @AND being DECLAREd and used, but can't see where it's being assigned a value.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 22, 2008 at 10:41 am
IF (@Status = 'All Active')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
I think you have your IF statement in the wrong location. Aren't you looking for something more like..
SET @SQLStr='SELECT * FROM #DLFiltered WHERE Active=1'
IF @status<>'All Active'
BEGIN
SET @SQLStr=@SQLStr+.....
.. The rest of your dymanic code
END
Brian
January 22, 2008 at 10:56 am
This is the entire code:
USE [TSDataWarehouse]
GO
/****** Object: StoredProcedure [consultantreports].[uspS_DownlineCoaching] Script Date: 01/17/2008 09:09:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------------------
STORED PROC: [consultantreports].[uspS_DownlineCoaching]
DESCRIPTION:returns the downline coaching view for a given consultant with current
demographic information
EXAMPLE:EXEC [consultantreports].[uspS_DownlineCoaching] '0000003','50','70'
RETURNS:0Success
Error NumberFailure
RELEASE:
VERSION:
BUILD:
-------------------------------------------------------------------------------
INPUT PARAMETERS :Req/Opt
@ConsultantIDREQUIRED
@AchieveLevelStartOPTIONAL
@AchieveLevelEndOPTIONAL
@LineStartOPTIONAL
@LineEndOPTIONAL
@statusOPTIONAL
@GreaterThanOPTIONAL
@LessThanOPTIONAL
@EqualToOPTIONAL
------------------------------------------------------------------------------
alorenzini2007-27-27Create
-------------------------------------------------------------------------------*/
DECLARE@ConsultantIDnVarChar(50)
DECLARE@AchieveLevelStart int
DECLARE@AchieveLevelEndint
DECLARE@LineStart varchar (10)
DECLARE @LineEndvarchar(10)
DECLARE@status varchar(10)
DECLARE@GreaterThan Decimal
DECLARE@LessThanDecimal
DECLARE @AndVARCHAR(200)
,@SQLStrNVARCHAR(4000)
,@PeriodDateDateTime
SET @ConsultantID = @ConsultantID
SET @PeriodDate = GETDATE()
Set @ConsultantID = '0000006'
SET @AchieveLevelStart = 10
SET @AchieveLevelEnd = 80
SET @LineStart = '1'
SET @LineEnd = '4'
SET @status = 'All Active'
SET @GreaterThan = 0
SET @LessThan = 20000
-- Achieve Level Filter
SET @AchieveLevelStart = @AchieveLevelStart
SET @AchieveLevelEnd = @AchieveLevelEnd
-- Downline level filter
SET @LineStart=@LineStart
SET @LineEnd= @LineEnd
-- Status Filter
SET @status = @status -- 'Inactive', 'All'
-- Sales level Filter
SET @GreaterThan = @GreaterThan
SET @LessThan = @LessThan
-- String that holds the AND statment for the
-- Sales level filter.
SET @AND = NULL
-- String that hold the SQL Execution statement
SET @SQLStr = NULL
-- Declare Local Variables
Declare @MonthStartDt As DateTime
DECLARE @MonthEndDt AS DateTime
DECLARE @QuarterStartDtASDateTime
DECLARE @QuarterEndDt AS DateTime
Declare @YearASDateTime
Declare @PeriodStartDt As DateTime
Declare @PeriodEndDt as DateTime
-- Breakdown @PeriodDate into Quarter and Months
SET @QuarterStartDt = DATEADD(quarter, DATEDIFF(quarter, 0, @periodDate), 0)
Set @QuarterEndDt = DATEADD(quarter, DATEDIFF(quarter, -1, @PeriodDate), -1)
SET @Year = DATEADD(yy, DATEDIFF(yy,0,@PeriodStartDt), 0)
SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @PeriodDate), 0)
Set @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, @PeriodDate), -1);
IF @MonthEndDt > GETDATE()
BEGIN
SET @MonthEndDt = GETDATE()
END;
-- Declare the Downline Recursive Query
With downline (ConsultantID,EffectiveDate, ConsultantName,DownLineLevel,
ConsultantXId,BumpupDate, DeactivationDate,CurrentLevelAchieveDate,
CurrentLevelXID, Active, BillToAddressLine1
,BillToAddressLine2,BillToCity,BillToState,BillToZip
,HomePhone,BusinessPhone,Fax,OtherPhone,
EmailAddress,SponsorXID, StatusID,NACDate )
AS
(
-- Anchor member defintion
SELECTA.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,0 as DownLineLevel
,A.ConsultantXID
,A.BumpupDate
,A.DeactivationDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.Active
,A.BillToAddressLine1
,A.BillToAddressLine2
,A.BillToCity
,A.BillToState
,A.BillToZip
,A.HomePhone
,A.BusinessPhone
,A.Fax
,A.OtherPhone
,A.EmailAddress
,A.SponsorXID
,A.StatusID
,A.NACDate
FROM dbo.uvwConsultantDownLine A
WHERE A.ConsultantID = @ConsultantID
AND @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate -- 1 Record
UNION ALL
--Recursive member definition
SELECTA.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,DownLineLevel + 1
,A.ConsultantXID
,A.BumpupDate
,A.DeactivationDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.Active
,A.BillToAddressLine1
,A.BillToAddressLine2
,A.BillToCity
,A.BillToState
,A.BillToZip
,A.HomePhone
,A.BusinessPhone
,A.Fax
,A.OtherPhone
,A.EmailAddress
,A.SponsorXID
,A.StatusID
,A.NACDate
FROM dbo.uvwConsultantDownLine AS A
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
WHERE @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate
) -- Appropriate records
-- Create the Temp table #Downline that returns the CTE results
SELECT A.DownLineLevel
,A.ConsultantID
,A.EffectiveDate
,UPPER(RTRIM(A.ConsultantName)) AS ConsultantName
,D.Title AS AchievedTitle
,A.ConsultantXID
,A.BumpupDate
,A.DeactivationDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.Active
,A.BillToAddressLine1
,A.BillToAddressLine2
,A.BillToCity
,A.BillToState
,A.BillToZip
,A.HomePhone
,A.BusinessPhone
,A.Fax
,A.OtherPhone
,A.EmailAddress
,A.SponsorXID
,A.StatusID
,A.NACDate
INTO #Downline
FROM DownLine AS A
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
A.CurrentLevelXID = D.XID
GROUP BY A.ConsultantID
,A.EffectiveDate
,A.ConsultantName
,DownLineLevel
,A.EmailAddress
,D.Title
,A.ConsultantXID
,A.Active
,A.BumpupDate
,A.DeactivationDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.BillToAddressLine1
,A.BillToAddressLine2
,A.BillToCity
,A.BillToState
,A.BillToZip
,A.HomePhone
,A.BusinessPhone
,A.Fax
,A.OtherPhone
,A.SponsorXID
,A.StatusID
,A.NACDate
Select
'OrigConsID' = @ConsultantID
,(Select ConsultantName FROM #Downline 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.AchievedTitle
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,ISNULL(Sum(Case
WHEN O.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS QuarterToDate_total
,ISNULL(Sum(Case
WHEN O.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS MonthToDate_Total
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.HomePhone
,D.BusinessPhone
,D.Fax
,D.OtherPhone
,D.EmailAddress
,ISNULL(r.RepFlag,' ')AS RepFlag
,D.SponsorXID
,D.Active
,D.StatusID
,'No_Of_Parties' = 0
,Convert(varchar(10),D.NACDate,101) AS AgreementDate
INTO #DlFiltered from #Downline D
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT Outer JOIN Repromotes r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'
WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND r.AchieveLevel >= 4
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.ConsultantXID
,D.EffectiveDate
,D.AchievedTitle
,D.BumpupDate
,D.CurrentLevelAchieveDate
,D.EmailAddress
,D.ConsultantXID
,D.CurrentLevelXID
,D.Active
,D.BillToAddressLine1
,D.BillToAddressLine2
,D.BillToCity
,D.BillToState
,D.BillToZip
,D.HomePhone
,D.BusinessPhone
,D.Fax
,D.OtherPhone
,r.RepFlag
,D.SponsorXID
,D.StatusID
,D.NACDate
-- Drop the temp table
--DROP TABLE #Downline
-- Set up And clause for Sales amount filter.
IF (@GreaterThan IS NOT NULL) AND (@LessThan IS NULL)
SELECT @And = ' AND MonthToDate_Total >= ' + CONVERT(VARCHAR(20),@GreaterThan) + ' '
ELSE IF (@LessThan IS NOT NULL) AND (@GreaterThan IS NULL)
SELECT @And = ' AND MonthToDate_Total <= ' + CONVERT(VARCHAR(20),@LessThan) + ' '
ELSE IF (@GreaterThan IS NOT NULL) AND (@LessThan IS NOT NULL)
SELECT @And = ' AND MonthToDate_Total BETWEEN ' + CONVERT(VARCHAR(20),@GreaterThan) + ' AND '+ CONVERT(VARCHAR(20),@LessThan) + ' '
-- All Active Filter
CASE @status ='All Active'
WHEN
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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
END
-- TIB Filter
IF (@Status = 'TIB')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
-- Special TIB Filter
IF (@Status = 'Special TIB')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
-- Special Grace Filter
IF (@Status = 'Grace')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
SELECT @SQLstr
-- New Filter
IF (@Status = 'New, Non Active Consultant')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
SELECT @SQLstr
--SELECT * FROM #DLFiltered WHERE Active In (0,1) AND CurrentLevelXID >= 20 AND DownlineLevel IN (SELECT * FROM dbo.ParseByComma(1))AND MonthToDate_Total = 400
EXEC sp_executesql @SQLstr;
DROP TABLE #Downline
DROP TABLE #DLFiltered
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 22, 2008 at 12:00 pm
Upon further investigation, I don't think the IF statements are working or the string concatenation is not doing it job:
Code:
IF (@Status = 'Grace')
Select @SQLStr
--SET @SQLStr = ' AND StatusID= ' + CAST(@Status AS VARCHAR(20))
Set @SQLStr = 'AND StatusID' +' = ''Grace'''
--SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
Select @SQLstr
Resulting string for @SQLStr coming into the IF statement which look like it duplicating values:
SELECT * FROM #DLFiltered WHERE Active = 1 AND DownlineLevel BETWEEN 1 AND 4 AND MonthToDate_Total BETWEEN 0 AND 20000 AND CurrentLevelXID BETWEEN 10 AND 80 AND DownlineLevel BETWEEN 1 AND 4 AND MonthToDate_Total BETWEEN 0 AND 20000 AND DownlineLevel BETWEEN 1 AND 4 AND MonthToDate_Total BETWEEN 0 AND 20000
Resulting string for @SQLstr coming out of the IF statement:
AND StatusID = 'Grace' AND CurrentLevelXID BETWEEN 10 AND 80 AND DownlineLevel BETWEEN 1 AND 4 AND MonthToDate_Total BETWEEN 0 AND 20000
Something is blowing up.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 22, 2008 at 12:09 pm
Could you post the DDL for the tables and some sample data? Read this article for more info on how to setup a script with test data: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Thanks
😎
January 22, 2008 at 12:18 pm
IF only determines if the statement is going to execute. Or the next batch of statements in a BEGIN END section. So in your code,
IF (@Status = 'TIB')
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
the IF will only affect the first SET statement. The other 2 will execute no matter what the variable equates to.
Try this.
IF (@Status = 'TIB')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 AND StatusID= ' + Convert(Varchar(20),@Status)
SET @SQLStr = @SQLStr + ' AND CurrentLevelXID BETWEEN ' + CONVERT(Varchar(2),@AchieveLevelStart ) + ' AND ' + CONVERT(Varchar(2),@AchieveLevelEnd )
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
END
Brian
January 22, 2008 at 12:27 pm
I changed the filter to look like this:
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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
END
Resulting @SQLstr String is:
SELECT * FROM #DLFiltered
WHERE Active = 1
AND StatusID = 'Grace'
AND CurrentLevelXID BETWEEN 10 AND 80
AND DownlineLevel BETWEEN 1 AND 4
AND MonthToDate_Total BETWEEN 0 AND 20000
This is correct because I can copy and paste it to a QA session and it runs. Yet when I run it in the script it does not return anything.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 22, 2008 at 1:32 pm
I think I see what is happening, if the StatusID does not having a matching record in the table it is returning all Actives instead of an expected NULL recordset.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 22, 2008 at 3:53 pm
I am being duuhhhhhh! The whole issue resolved itself when I lengthen the @status variable from 10 to 20. The blessed variable wasn't long enough. Thanks for everybodys help.:hehe:
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply