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!
fskilnikAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
QlemoConnect With a Mentor DeveloperCommented:
If you really want to check for NULL, do so, and do not replace NULL with a value.
  If @IsForAudience is not null

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
If ISNULL(@IsForAudience, 0) > 0

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

But if @IsForAudience could be a negative number use:

If ISNULL(@IsForAudience, 0) !> 0
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
fskilnikAuthor Commented:
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.
0
 
QlemoDeveloperCommented:
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).
0
 
fskilnikAuthor Commented:
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!!!!
0
 
QlemoDeveloperCommented:
The 2nd answer is still not correct. It only allows negative numbers and zero (and NULL!).
0
 
QlemoDeveloperCommented:
Proof with
declare @isForAudience int;
set @isForAudience = 1
if isnull(@isForAudience, 0) !> 0
  Print "True for "+ @isForAudience

Open in new window

and different values.
0
 
fskilnikAuthor Commented:
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.
0
 
fskilnikAuthor Commented:
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.
0
 
fskilnikAuthor Commented:
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!   :)
0
 
QlemoDeveloperCommented:
Thanks for the kudos. It was less about the points and more about showing you were wrong before running into trouble ;-).
0
 
fskilnikAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.