Nested IF Statement

[raven4]

I am a novice script writer.

I am trying to write a stored procedure that will include some nested IF statements. The basic concept of the first statment in plain English is:

If condition A is true AND condition B is true, then execute the next If statement:

If Date A is greater than Date B, then do an update.​

Here's my statement so far:

SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[UpdateCheck] (
@DuplicateId varchar(10),
@MergeToId varchar(10)
)
as


declare @MaxDateDuplicateId datetime
declare @MaxDateMergeToId datetime
declare @MaxChangeDuplicateUser varchar(10)
declare @MaxChangeMergeUser varchar(10)

select @MaxDateDuplicateId = max(DATE_TIME) from Name_Log where ID = @DuplicateId
select @MaxDateMergeToId = max(DATE_TIME) from Name_Log where ID = @MergeToId
select @MaxChangeDuplicateUser = USER_ID from (select m.*, row_number() over (partition by ID order by date desc) as rn from
Name_log m) m2 where m2.rn = 1 and ID = @DuplicateID
select @MaxChangeMergeUser = USER_ID from (select m.*, row_number() over (partition by ID order by date desc) as rn from
Name_log m) m2 where m2.rn = 1 and ID = @MergeToID

IF
@MaxChangeDuplicateUser in (select UserID from UserMain um, Relationship r
where um.contactmaster = r.target_id and r.relation_type = '_ORG-ADMIN')
AND @MaxChangeMergeUser in ((select UserID from UserMain um, Relationship r
where um.contactmaster = r.target_id and r.relation_type = '_ORG-ADMIN')
BEGIN
if (@MaxDateDuplicateId > @MaxDateMergeToId)
BEGIN
Update m
set m.member_type = d.member_type
from Name m, Name d where m.ID = @MergeToID and d.ID = @DuplicateID
END
END


I'm getting the following error:

Incorrect syntax near the keyword 'BEGIN'.

I don't know if it's a problem with my conditional statement before the BEGIN or with the nested IF after the BEGIN.

Thanks.

 

[gmmastros]

Looks like you have an extra open parenthesis:

ALTER proc [dbo].[UpdateCheck] (
@DuplicateId varchar(10),
@MergeToId varchar(10)
)
as


declare @MaxDateDuplicateId datetime
declare @MaxDateMergeToId datetime
declare @MaxChangeDuplicateUser varchar(10)
declare @MaxChangeMergeUser varchar(10)

select @MaxDateDuplicateId = max(DATE_TIME) from Name_Log where ID = @DuplicateId
select @MaxDateMergeToId = max(DATE_TIME) from Name_Log where ID = @MergeToId
select @MaxChangeDuplicateUser = USER_ID from (select m.*, row_number() over (partition by ID order by date desc) as rn from 
Name_log m) m2 where m2.rn = 1 and ID = @DuplicateID
select @MaxChangeMergeUser = USER_ID from (select m.*, row_number() over (partition by ID order by date desc) as rn from 
Name_log m) m2 where m2.rn = 1 and ID = @MergeToID

IF
@MaxChangeDuplicateUser in (select UserID from UserMain um, Relationship r
where um.contactmaster = r.target_id and r.relation_type = '_ORG-ADMIN')
AND @MaxChangeMergeUser in [!] -- > ( < -- [/!](select UserID from UserMain um, Relationship r
where um.contactmaster = r.target_id and r.relation_type = '_ORG-ADMIN')
BEGIN 
if (@MaxDateDuplicateId > @MaxDateMergeToId)
BEGIN
Update m
set m.member_type = d.member_type
from Name m, Name d where m.ID = @MergeToID and d.ID = @DuplicateID
END
END

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[raven4]

Oh my gosh! So simple. Thank you!

 

[Andrzejek]

raven4,
For last 10 years or so hopefully you got some help here from other TT members.
Maybe it is time to show some appreciation and start using "[blue]Great Post![/blue]" link to award a star for help received?



---- Andy

There is a great need for a sarcasm font.

 

[DaveInIowa]

I'd also move the check for @MaxDateDuplicateId > @MaxDateMergeToId to the outer IF statement. May as well do the simple variable comparison first before any statements that access tables.

 

[raven4]

I haven't been on these boards a ton in 10 years - 14 total posts in that time - and not at all between 2013 and 2017. Nevertheless, thank you for the etiquette lesson and consider it done. My apologies to anyone who was offended by my lack of recognition.

 

[raven4]

Thank you, DaveInIowa, for the suggestion but the user who made the post is the priority thing to check, even if the date was older on the change. Only when the user matches is the date in play.

 

[gmmastros]

raven4,

Dave makes a good point. Forget for a moment that there is any business logic involved and only consider the programming logic. Basically, the update statement only occurs if 3 conditions are met. It makes sense to check the condition the executes the fastest first. The inner IF condition is simply comparing 2 scalar values whereas the outer 2 IF statements are comparing a scalar value to data in a table.

Basically, Dave's suggestion is to perform the quicker comparison first. If the quicker comparison returns FALSE, the 2 slower comparisons won't need to be performed and the overall query will take less time.

Clearly, when writing SQL Code, the #1 most important thing is data integrity (returning the correct data). A secondary concern should be performance. If you make the change Dave is suggestion, the query will never be any slower than it already is, but will sometimes be faster depending on the data. That makes it worthwhile in my opinion.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom