July 11, 2010 at 11:58 pm
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.
July 12, 2010 at 12:26 am
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
July 12, 2010 at 12:54 am
Hi,
Thank you for the Reply, Is there any other way i can do this, Other than IF ELSE and checking @@RowCount.
July 12, 2010 at 1:49 am
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
July 12, 2010 at 5:45 am
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