Prolem with IF ELSE Statement

Question

Prolem with IF ELSE Statement

Vinay Theethira

Right there with Babe

Points: 775
More actions
July 11, 2010 at 11:58 pm

#223227

Hi All,
I have a Procedure which should return only 1 Record on selecting record according to Priority.
BEGIN
DECLARE @Accounts nvarchar(MAX)
SELECT @Accounts = ISNULL([map_user_modules].[Accounts], '') FROM [map_user_modules]
WHERE [map_user_modules].[id_user] = 231 AND [map_user_modules].[id_module] = 5
IF ((@Accounts = '') OR (@Accounts = 'All'))
SELECT @Accounts = null
IF @@ROWCOUNT =0
BEGIN
Select @@ROWCOUNT as Priority2
END
ELSE IF @@ROWCOUNT = 0
BEGIN
Select @@ROWCOUNT as Priority3
END
ELSE IF @@ROWCOUNT = 0
BEGIN
Select @@ROWCOUNT as Priority4
END
The if conditions should work sequentially.
1.If first if conditions works(i.e Select @@ROWCOUNT as Priority2),then other select statement should not return record. Similarly for Second condition.
Can anyone suggest a solution for this, because, the query above is always taking the first if statement, even though it is returning 0 records,it is not going to other Else If statements.

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

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

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 1

I think you are concentrating the @@ROWCOUNT corresponding to the SELECT (the section before the IF section starts).

Here what is happening is the statement SELECT @Accounts = null makes the @@ROWCOUNT to a value > 0 , so it wont fall in to the statement Select @@ROWCOUNT as Priority2 . But the check IF @@ROWCOUNT =0 didn't return any record so it makes the @@ROWCOUNT = 0 so that the crontrol falls into

ELSE IF @@ROWCOUNT = 0

BEGIN

Select @@ROWCOUNT as Priority3

END

Here due to the SELECT statement again the @@ROWCOUNT is >0 so that it wont fall in to the next section.

I think better you can assign the initial @@ROWCOUNT in to a variable and work on that.

Thanks & Regards,
MC

Vinay Theethira Right there with Babe Points: 775 More actions · Answer 2

Hi,

Thank you for the Reply, Is there any other way i can do this, Other than IF ELSE and checking @@RowCount.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 3

Instead of IF ELSE you can use CASE statement, but here all the cases are with same condition ie @@ROWCOUNT = 0 , so no point in using that.

Check the requirement correctly , ( because if you check the code every condition is checking whether @@ROWCOUNT = 0..... I didtnt really understand why you want to do it like that.)

Then assign the value to a variable then use IF ELSE or CASE (if there are more than one condition

)

Thanks & Regards,
MC

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Hard to really help you based solely on the code provided. We could provide much better assistance if you could post the DDL (CREATE TABLE) statements for the tables involved, sample data (as INSERT INTO statements) for each table, and expected results based on the sample data. As you have multiple conditions to test, you should probable provide separate test sets for each condition. The sample data does not need to be large, just enough data to represent the problem domain properly.