January 24, 2008 at 1:23 pm
How can I insert the resultset from dynamic SQL into a temp table for further processing based on my code below:
IF (@Status = 'All 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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
END
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!
January 24, 2008 at 10:14 pm
any temp table created in a stored procedure vanishes after the stored procedure completes. so you can't use sp_executesql to create a temp table. you'll need to create the temp table before the call and use sp_executesql to populate it.
also, sp_executesql requires nchar text (e.g.: "declare @statement nvarchar(250)").
January 25, 2008 at 12:32 am
This one works for me if this is what you are looking for, the idea is to create the temporary table before and insert the rows later.
CREATE TABLE #test (val INT);
INSERT INTO #test (val) VALUES (3);
CREATE TABLE #test3 (val INT);
DECLARE @SQLStr nvarchar(max)
SET @SQLStr = 'insert into #test3 SELECT * FROM #test';
EXEC sp_executesql @SQLstr;
select * from #test3;
drop table #test3;
drop table #test;
January 25, 2008 at 8:30 am
based on the inpout from everybody I think I need tro find another way to do this. What I need is to have two record set returned. One will have the actual rows and then based off the first recordset a summary of those rows. THis is the code as it stands now which returns the 1st row set:
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
DECLARE@ConsultantIDnVarChar(50)
DECLARE@AchieveLevelStart int
DECLARE@AchieveLevelEndint
DECLARE@LineStart varchar (10)
DECLARE @LineEndvarchar(10)
DECLARE@Status varchar(30)
DECLARE@GreaterThan Decimal
DECLARE@LessThanDecimal
DECLARE @AndVARCHAR(200)
,@SQLStrNVARCHAR(4000)
,@PeriodDateDateTime
SET @ConsultantID = @ConsultantID
SET @PeriodDate = GETDATE()
Set @ConsultantID = '0000112'
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
-- 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
,CONVERT(Char(10),Max(O.OrderCreateDate),101) AS 'LastOrderDate'
,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
,'StatusID' = CASE
WHEN StatusID ='Active' THEN ''
WHEN StatusID = 'Home Office' THEN ''
WHEN StatusID = 'New, Non Active Consultant' THEN ''
ELSE StatusID
END
,'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) + ' '
SET @SQLStr = 'SELECT * FROM #DLFiltered WHERE Active = 1 '
-- 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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
END
-- All Reactivation Filter
IF (@Status = 'All 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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
END
-- Execute Dynamic SQL
EXEC sp_executesql @SQLstr;
DROP TABLE #Downline
DROP TABLE #DLFiltered
I need to now generate a summary also based on the rowset produced by this code.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 25, 2008 at 8:40 am
I would have changed the process to get the data myself. your proc is what my boss calls "putting wings and wheels on a horse"...that is, using one procedure to try and run,jump, or fly all with the same tool.
I would instead break your procedure into multiple smaller procedures, and use either a master procedure, or logic in the business layer to decide which one to call, based on the empty parameters you are checking.
so if you need to check one kind of criteria, it calls one sub proc or another, instead of a bigger, do all dynamic procedure you are currently using.
Lowell
January 25, 2008 at 9:15 am
It seems that your need for dynamic sql can be eliminated with a bit of rework. For example, this 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) + ' '
can be reduced to this:
and MonthToDate_Total >= isnull(@GreaterThan,@absoluteMin)
and MonthToDate_Total <= isnull(@LessThan,@absoluteMax)
@absoluteMin and @absoluteMax are the lowest and highest values for your datatype and should be declared and set in the proc.
likewise, all of your filters could be accomplished with UNIONS:
-- 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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @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 + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),@LineStart ) + ' AND ' + CONVERT(Varchar(2),@LineEnd )+ @AND
END
becomes this (unless i've misread your logic)
-- In Reactivation 30 Day Filter
SELECT * FROM #DLFiltered WHERE Active = 1
AND StatusID = @Status
AND CurrentLevelXID BETWEEN @AchieveLevelStart and @AchieveLevelEnd )
AND DownlineLevel BETWEEN @LineStart AND @LineEnd
AND (@Status = 'In Reactivation 30 Day')
UNION
-- In Reactionation 1 Quarter Filter
SELECT * FROM #DLFiltered WHERE Active = 1
AND StatusID= @Status
AND CurrentLevelXID BETWEEN @AchieveLevelStart ) AND @AchieveLevelEnd
AND DownlineLevel BETWEEN @LineStart AND @LineEnd
AND (@Status = 'In Reactivation 1 Quarter')
January 25, 2008 at 9:54 am
so all my filters would be come a union?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 25, 2008 at 10:35 am
I think you miss read the code
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) + ' '
WHERE you said
and MonthToDate_Total >= isnull(@GreaterThan,@absoluteMin)and MonthToDate_Total <= isnull(@LessThan,@absoluteMax)
The And is actually a variable @And that get appended to the SQL String. How is that handled if I remove the Dynamic SQL?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 25, 2008 at 10:45 am
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) + ' '
WHERE you said
and MonthToDate_Total >= isnull(@GreaterThan,@absoluteMin)and MonthToDate_Total <= isnull(@LessThan,@absoluteMax)
The And is actually a variable @And that get appended to the SQL String. How is that handled if I remove the Dynamic SQL?
move the 2 MonthToDate comparisons to each UNION. Dynamic SQL isn't necessary.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply