Solved

procedure detail

Posted on 2015-02-22
14
238 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 70

Accepted Solution

by:
Qlemo earned 500 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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 70

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 70

Expert Comment

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

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 70

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

710 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