conditions in IF statement did not get checked
-
I have this if statement:
IF((@zipcodeEXIST = @zipcode) and (@weekInfo = @week))
which doesnot seem that both conditions are being checked. Should I rewrite my IF statement? Thanks for helping.
-
Can you post a reproduction illustrating the problem? What are the values of the variables, and why don't you think it's working?
--
Adam Machanic
whoisactive -
Here's the whole stored proc:
CREATE Procedure sp_updateInvalidziplist
(
@zipcode varchar(5),
@sessionID varchar(50),
@dateTime datetime,
@ani varchar(10),
@did varchar(10),
@week varchar(2)
)
As
set nocount on
DECLARE @zipcodeEXIST varchar(5)
DECLARE @weekInfo varchar(2)
SELECT @zipcodeEXIST = zipcode from invalidziplist where zipcode = @zipcode;
SELECT @weekInfo = week from invalidziplist where zipcode = @zipcode;
if(@zipcodeEXIST is null)
begin
INSERT INTO invalidziplist (zipcode,requestCount,sessionID,dateTime,ani,did,week)
values (@zipcode,1,@sessionID,@dateTime,@ani,@did,@week)
end
else
IF((@zipcodeEXIST = @zipcode) and (@weekInfo = @week))
begin
update invalidziplist
set requestCount = requestCount + 1,
sessionID = @sessionID,
dateTime = @dateTime,
ani = @ani,
did = @did
where zipcode = @zipcode;
end
else
begin
INSERT INTO invalidziplist (zipcode,requestCount,sessionID,dateTime,ani,did,week)
values (@zipcode,1,@sessionID,@dateTime,@ani,@did,@week)
end
GO
pretty much just want to check if the zipcode and week number is the same...and if it is just do that update....but it inserts a new row if they are equal...that's why I was wondering about my syntax
Thanks for helping.
-
Sometimes it helps to be more explicit. Try the following:
SELECT @zipcodeEXIST = (SELECT zipcode FROM invalidziplist WHERE zipcode = @zipcode)
SELECT @weekInfo = (SELECT week FROM invalidziplist WHERE zipcode = @zipcode)
You can also try:
IF ISNULL( RTRIM( LTRIM( @zipcodeEXIST)), 'AAAAA') = 'AAAAA'
I wasn't born stupid - I had to study.
-
Looking at the code there doesn't seem to be an issue with it, however I would suggest changing to using the exists clause.
Something like:
CREATE Procedure sp_updateInvalidziplist
@zipcode varchar(5),
@sessionID varchar(50),
@dateTime datetime,
@ani varchar(10),
@did varchar(10),
@week varchar(2)
As
set nocount on
DECLARE @zipcodeEXIST varchar(5),
@weekInfo varchar(2)
--if the zipcode and week already exist update the record
IF exists( select zipcode from invalidziplist where zipcode = @zipcode and [week] = @week)
begin
update invalidziplist
set requestCount = requestCount + 1,
sessionID = @sessionID,
dateTime = @dateTime,
ani = @ani,
did = @did
where zipcode = @zipcode
end
else --the zipcode week combination do not exist, create a new record
begin
INSERT INTO invalidziplist (zipcode,requestCount,sessionID,dateTime,ani,did,week)
values (@zipcode,1,@sessionID,@dateTime,@ani,@did,@week)
end
Hope this helps
c
If the phone doesn't ring...It's me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply