Link to home
Start Free TrialLog in
Avatar of fskilnik
fskilnikFlag for Brazil

asked on

procedure detail

Hi there,

The following stored procedure:

--------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[net_update_sproc_test]
(
      @questDBID int,
      @isForAudience int = null
)
AS
BEGIN
  If ISNULL(@IsForAudience, 0) != 0
  BEGIN
  UPDATE dbo.QuestionsDB
  SET
  IsForAudience = @IsForAudience
  WHERE QuestDBID = @questDBID
  END
END

GO
-------------------------------------------------------------

works as expected when the IsForAudience input is (say) null (not given) or any positive integer.

The problem is when the input is 0 (zero)... in this case the condition seems to get true, when it should get false...

(There is a difference between the 0 -- zero -- value and null -- non-existence, no-given -- value, right?!)

My question: how should I change the procedure so that it works as expected, that is, respecting the 0 (zero) input as a valid input and, therefore, updating the table field with the (proper) 0 (zero) value?

Thanks!

P.S.: I will be back approx. 1h from now... thanks!
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

If ISNULL(@IsForAudience, 0) > 0

You were checking for null only. The above checks for both null and 0
Just to let you know, my last post was revised.

But if @IsForAudience could be a negative number use:

If ISNULL(@IsForAudience, 0) !> 0
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fskilnik

ASKER

Although eghtebas solved the problem throughly, I guess Qlemo´s answer is exactly what I had in mind in the first place... (not to mention that its simplicity made me understand better the different possibilities)

Thank you both for your great answers!

Regards,
fskilnik.
No, http:#a40624599 is not correct. That considers only positive numbers. Zero is not positive, but should be valid. Negative numbers should be valid, too. Only NULL is not valid (= n/a).
Hi, Qlemo.

In the first conditional presented, we would like 0 to go "inside" the If clause... but that doesn´t happen because ISNULL(@IsForAudience, 0) will turn out to be 0 and then 0 > 0 is false...

But the fact is that the second eghtebas suggestion works, because 0 !>0 I guess means 0 < = 0 and this is true, correct?!

Therefore I guess I was fair, in the sense that he SOLVED the problem (in his second post) but your solution is really simpler.

I am sorry if you think I should have divided the points in another way but the fact is that his solution was what I needed (the second one) and even if I had not the pleasure to see yours, I could have got my problem solved and I would understand what the expression ISNULL(@IsForAudience, 0)  means... and that was something I didn´t get at the time I posted my question...

Regards,
fskilnik.

P.S.: Oh... now I see the main reason for your criticism... till this moment I haven´t seen that I put his FIRST answer as the correct one... MY mistake... sorry for that!!!!
The 2nd answer is still not correct. It only allows negative numbers and zero (and NULL!).
Proof with
declare @isForAudience int;
set @isForAudience = 1
if isnull(@isForAudience, 0) !> 0
  Print "True for "+ @isForAudience

Open in new window

and different values.
First of all, thanks a LOT to insist on this matter, Qlemo.

You ARE right. When @IsForAudience is NULL, then ISNULL(@IsForAudience, 0) turns out to be zero, then the IF clause will occur and we will get an error...

I will ask a moderator to reopen this question for me and you will get the full marks. I guess this is absolutely fair and I am really sorry for not checking all that properly. As ironically as it may be, I changed my code to YOUR suggestion because it was at least simpler. Now I see it was the CORRECT one, really.

I am really sorry, and I hope the moderators will let me do justice.

Regards,
fskilnik.
Thanks also for the "checking code"!   As I told you (and proved above taking special/particular cases), now I am sure the logic from the previous posts were flawed.
I would like to mention (here, "in public") that I WROTE for the moderators (asking to reopen the post) BEFORE Qlemo posted his last interaction.  I didn´t know he was/is a Topic Advisor untill his last interaction (above), by the way.

In other words, he (Qlemo) could NOT be MORE ethical.  I am really impressed on that. This is one more reason to believe EE is THE place!   :)
Thanks for the kudos. It was less about the points and more about showing you were wrong before running into trouble ;-).
I know that, Qlemo!

I am 44 years old... that means I am old enough to know that your behavior deserves even more congratulations than your (marvellous) expertise in "computing matters". (And your willing to be sure the proper correct answer is clear is also great, of course.) As we say in hebrew, "Kol Hakavod!", meaning all merits, all recognition (for your behavior)!

Regards,
fskilnik.