Off IF...ELSE Behavior

Question

Off IF...ELSE Behavior

Jody Claggett-376930

Ten Centuries

Points: 1152
More actions
September 10, 2008 at 4:02 pm

#192575

I'll try to explain this in the simplest why I can, what I am really after is why the IF/ELSE code is acting this way.
I have multiple IF/ELSE statements that end up inserting data into a temp table if the condition is met. When I run the code however, the INSERT INTO #temp statement seems to be running even in a false evaluation of IF/ELSE.
Here is an abbreviated version of the code:
BEGIN
IF OBJECT_ID('tempdb.dbo.#AEMQuotaYTD') IS NOT NULL
DROP TABLE #AEMQuotaYTD
END
BEGIN
DECLARE @curMonth AS varchar(20)
SET @curMonth = 'August, 2008'
IF @curMonth = 'July, 2008'
BEGIN
SELECT
Alias,
FiscalYear,
SUM(QuotaAmt) AS QuotaAmt
INTO #AEMQuotaYTD
FROM tblRepQuota
WHERE FiscalYear = '2009' AND RevType = 'AEM'
AND FiscalMonth IN ('July')
GROUP BY
Alias,
FiscalYear
END
ELSE IF @curMonth = 'August, 2008'
BEGIN
SELECT
Alias,
FiscalYear,
SUM(QuotaAmt) AS QuotaAmt
INTO #AEMQuotaYTD
FROM tblRepQuota
WHERE FiscalYear = '2009' AND RevType = 'AEM'
AND FiscalMonth IN ('July', 'August')
GROUP BY
Alias,
FiscalYear
END
END
When I run this, I get the following error:
There is already an object named '#AEMQuotaYTD' in the database.
It seems to me that the evaluation of the first statement actually creates the temp table even though the value is false. Can anyone help determine the cause?
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Actually, the message is from the parser. After you run this and get that error you will notice that #AEMQuotaYTD does not really exist.

Create an empty table and use insert into.

BEGIN

IF OBJECT_ID('tempdb.dbo.#AEMQuotaYTD') IS NOT NULL

DROP TABLE #AEMQuotaYTD

END

-- even better if you define the columns yourself

select top 0 * into #AEMQuotaYTD FROM tblRepQuota

BEGIN

DECLARE @curMonth AS varchar(20)

SET @curMonth = 'August, 2008'

IF @curMonth = 'July, 2008'

BEGIN

insert into #AEMQuotaYTD

SELECT

Alias,

FiscalYear,

SUM(QuotaAmt) AS QuotaAmt

-- INTO #AEMQuotaYTD

FROM tblRepQuota

WHERE FiscalYear = '2009' AND RevType = 'AEM'

AND FiscalMonth IN ('July')

GROUP BY

Alias,

FiscalYear

END

ELSE IF @curMonth = 'August, 2008'

BEGIN

insert into #AEMQuotaYTD

SELECT

Alias,

FiscalYear,

SUM(QuotaAmt) AS QuotaAmt

-- INTO #AEMQuotaYTD

FROM tblRepQuota

WHERE FiscalYear = '2009' AND RevType = 'AEM'

AND FiscalMonth IN ('July', 'August')

GROUP BY

Alias,

FiscalYear

END

Jody Claggett-376930 Ten Centuries Points: 1152 More actions · Answer 2

My apologies, I should have been more specific.

The first piece of code runs fine and effectively drops the table.

When the second batch of code is run, that's when I get the error message. So in reality, you can ignore the first BEGIN/END code block.

_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Sorry... deleted this post... I made an error in the code. Repaired further on below...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Crud, I made a mistake on the fiscal year :blush:... don't use the code above... I'll post a correction soon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Heh... you want to do 12 "IF's" per query, Jody? No, no need for IF's here. You didn't post any test data so the outer query in the following is untested, but I did test everything else including the mini-date-table subquery. As usual, details are in the comments...

Here we go...

--===== If the temp table already exists, drop it.

-- This should be commented out in a production environment

IF OBJECT_ID('TempDB.dbo.#AEMQuotaYTD') IS NOT NULL

DROP TABLE #AEMQuotaYTD

--===== Declare and identify the "current month"

DECLARE @CurMonth AS VARCHAR(20),

@FiscalYear AS CHAR(4),

@FiscalQtr AS INT

--===== Set the variables

SELECT @CurMonth = 'August, 2008',

@FiscalYear = DATEDIFF(mm,'18990701',@CurMonth)/12+1900,

@FiscalQtr = DATEDIFF(qq,'18990701',@CurMonth)%4 + 1

--===== Sanity check

SELECT @CurMonth,@FiscalYear,@FiscalQtr

--===== Do the problem with no IF's

SELECT Alias,

FiscalYear,

SUM(QuotaAmt) AS QuotaAmt

INTO #AEMQuotaYTD

FROM dbo.tblRepQuota rq

INNER JOIN

(--==== Create a mini-date table that should probably be a permanent table

SELECT TOP 12

DATENAME(mm,DATEADD(mm,Number,'19000101')) AS MonthName,

DATEPART(qq,DATEADD(mm,Number,'18990701')) AS FiscalQtr

FROM Master.dbo.spt_Values WITH (NOLOCK) --Used as a Tally Table

WHERE Type = 'P'

AND Number < 12) fy

ON rq.FiscalMonth = fy.MonthName

WHERE rq.FiscalYear = @FiscalYear

AND rq.RevType = 'AEM'

AND fy.FiscalQtr = @FiscalQtr

GROUP BY

rq.Alias,

rq.FiscalYear

WHERE FiscalQtr = @FiscalQtr

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jody Claggett-376930 Ten Centuries Points: 1152 More actions · Answer 6

Thanks Jeff, you've helped me out a few times in the past couple of weeks. I really appreciate it.

I like the idea of not using 12 IF's, but it looks like your code sample is suming the data based on the fiscal quarter.

What I am looking for is to sum the YTD quota up to the current fiscal month. I've provided some a sample data table in this code that will help everyone understand the objective.

The @curMonth variable uses my fiscal calender where the current date is between fiscal month begin and fiscal month end. That code is in the sample as well, but commented out. In the sample that variable is set to 'October, 2008', which means the sum of the YTD quota should be $4000.

--===== If the temp table already exists, drop it.

-- This should be commented out in a production environment

IF OBJECT_ID('TempDB.dbo.#AEMQuotaYTD') IS NOT NULL

DROP TABLE #AEMQuotaYTD

IF OBJECT_ID('TempDB.dbo.#tmpQuota') IS NOT NULL

DROP TABLE #tmpQuota

CREATE TABLE #tmpQuota

(

Aliasvarchar(20) NULL,

FiscalYearINT NULL,

FiscalMonthvarchar(20) NULL,

RevTypevarchar(3) NULL,

QuotaAmtINT

)

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'July', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'August', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'September', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'October', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'November', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'December', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'January', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'February', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'March', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'April', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'May', 'AEM', '1000')

INSERT INTO #tmpQuota VALUES('jsmith', '2009', 'June', 'AEM', '1000')

--SELECT * FROM #tmpQuota

--===== Declare and identify the "current month"

DECLARE @CurMonth AS VARCHAR(20),

@FiscalYear AS CHAR(4),

@FiscalQtr AS INT

--===== Set the variables

SELECT @CurMonth = 'October, 2008' --(

--SELECT FiscalMonth FROM vFiscalCalDays

--WHERE GETDATE() BETWEEN FiscalMonthBegin and FiscalMonthEnd

--)

,

@FiscalYear = DATEDIFF(mm,'18990701',@CurMonth)/12+1900,

@FiscalQtr = DATEDIFF(qq,'18990701',@CurMonth)%4 + 1

--===== Sanity check

--SELECT @CurMonth,@FiscalYear,@FiscalQtr

--===== Do the problem with no IF's

SELECT Alias,

FiscalYear,

SUM(QuotaAmt) AS QuotaAmt

INTO #AEMQuotaYTD

FROM dbo.#tmpQuota rq

INNER JOIN

(

SELECT TOP 12

DATENAME(mm,DATEADD(mm,Number,'19000101')) AS MonthName,

DATEPART(qq,DATEADD(mm,Number,'18990701')) AS FiscalQtr

FROM Master.dbo.spt_Values WITH (NOLOCK) --Used as a Tally Table

WHERE Type = 'P'

AND Number < 12

) fy

ON rq.FiscalMonth = fy.MonthName

WHERE

rq.FiscalYear = @FiscalYear

AND rq.RevType = 'AEM'

AND fy.FiscalQtr = @FiscalQtr

GROUP BY

rq.Alias,

rq.FiscalYear

select * from #AEMQuotaYTD

_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

Jody Claggett (9/11/2008)
...but it looks like your code sample is suming the data based on the fiscal quarter.
What I am looking for is to sum the YTD quota up to the current fiscal month.

Correct... it's summing fiscal quarter data. Thank you for the clarification... I'll be back...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

Here you go, Jody...

--===== If the temp table already exists, drop it.

-- This should be commented out in a production environment

IF OBJECT_ID('TempDB.dbo.#AEMQuotaYTD') IS NOT NULL

DROP TABLE #AEMQuotaYTD

IF OBJECT_ID('TempDB.dbo.#tmpQuota') IS NOT NULL

DROP TABLE #tmpQuota

CREATE TABLE #tmpQuota

(

Alias varchar(20) NULL,

FiscalYear INT NULL,

FiscalMonth varchar(20) NULL,

RevType varchar(3) NULL,

QuotaAmt INT