Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

procedure detail

Posted on 2015-02-22
14
Medium Priority
?
250 Views
Last Modified: 2015-03-30
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!
0
Comment
Question by:fskilnik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
14 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40624599
If ISNULL(@IsForAudience, 0) > 0

You were checking for null only. The above checks for both null and 0
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40624603
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
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 40624699
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:fskilnik
ID: 40624720
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 40624723
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
 

Author Comment

by:fskilnik
ID: 40624746
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 40624765
The 2nd answer is still not correct. It only allows negative numbers and zero (and NULL!).
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 40624769
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
 

Author Comment

by:fskilnik
ID: 40624779
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
 

Author Comment

by:fskilnik
ID: 40624787
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
 

Author Comment

by:fskilnik
ID: 40624818
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 40624838
Thanks for the kudos. It was less about the points and more about showing you were wrong before running into trouble ;-).
0
 

Author Comment

by:fskilnik
ID: 40624855
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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question