update query with IF else statement

Question

update query with IF else statement

SkyBVI

SSCoach

Points: 15128
More actions
January 14, 2011 at 8:17 am

#232219

I have a stored proc as
as
Select
EMPLOYID,
Case
when sum(a.UPRTRXAM) = null
then 0
else sum(a.UPRTRXAM)
end
as
TaxYTD,
@Month as CurrMonth
Into
xyztaxcurrentmonth
from
UPR30300 a
where
DatePart(mm,Chekdate) = @Month /*---1st line */
and DatePart(yy,ChekDate) = 2011 /* ---2nd line */
and PAYROLCD = 'PAYE-T' /* ---3rd line */
group by
employid
--- In this in the where clause the '2011' is hard coded, I dont want that and also, I want to add some piece of code in it.
The code I want to add in where clause is
If @month = 12
begin
datepart(yy,chekdate) = datepart (yy,getdate()) -1
else
begin
datepart(yy,chekdate) = datepart (yy,getdate())
/* Also, the 1st and 3rd line should be executed irrespective of the if else statements...
In short ---I want the 1st line, 3rd line and this above code (whcih i want to add) all in the where clause . Plz note :The value of @month, I am getting from a different procedure */
I am stuck in this
Thanks.
Regards,
Sushant
DBA
West Indies
Regards
Sushant Kumar
MCTS,MCP

Viewing 6 posts - 1 through 5 (of 5 total)

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

Nevyn SSChampion Points: 14210 More actions · Answer 1

FYI, you can format IF logic into a WHERE clause using CASE if you need to.

In the above example, though, you probably shouldn't be using all those dateparts in the select at all, as they will make your query potentially much slower.

Since this is a stored proc, a better approach would be have code before the select statement that sets up 2 date variables for the start and end of the range of dates you want to find, and then the WHERE statement in your select can just check that chekdate is between those values. Then you could do away with the 1st AND 2nd line from your example and your query would likely be much faster, especially if chekdate has an index on it.

tertiusdp SSCommitted Points: 1569 More actions · Answer 2

I think Nevyn is on the right track. I would just be careful as SQL server cannot use the parameters calculated inside a stored proc to calculate a good plan, so it will make assumptions.

SkyBVI SSCoach Points: 15128 More actions · Answer 3

I wrote a diff procedure to pass values in the above proc

Declare @Month int,

@Year int

Set @Month = DatePart(mm,Getdate()) -1

if @Month = 0

set @Month =12

set @Year = datepart(yy,getdate())-1

else

set @Year = datepart(yy,getdate())

print '@year';

I am getting error as incorrect sytax near else.

Any ideas,

Regards,

Sushant

DBA

West Indies

Regards
Sushant Kumar
MCTS,MCP

tertiusdp SSCommitted Points: 1569 More actions · Answer 4

You needed begin and end around your if statement, otherwise it only evaluates one statement as part of the if.