Prolem with IF ELSE Statement

  • 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.

  • 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

  • Hi,

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

  • 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

  • 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.

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

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