Solved

procedure detail

Posted on 2015-02-22
14
222 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
  • 6
  • 5
  • 2
14 Comments
 
LVL 33

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 33

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 68

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
 

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 68

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 68

Expert Comment

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

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 68

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now