SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with NULL columns		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shawnmolloy
Yak Posting Veteran

USA
93 Posts
Posted - 06/14/2007 :  01:21:50  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
This procedure doesnt increment the "Views" column like it should when the value is NULL (not zero)

CREATE procedure [dbo].[sp_theDb_image_addView]
@imageId int
AS

SELECT [views],
CASE WHEN [views] is null then 1
END

FROM theDb_image WHERE imageId=@imageI

UPDATE theDb_image SET [views] = [views] + 1 WHERE imageId=@imageI

The CASE statement apparently isn't working.... any advice?

Thanks.

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 06/14/2007 :  01:26:34  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Try this:

UPDATE theDb_image SET [views] = Coalesce([views], 0) + 1 WHERE imageId=@imageI


I am not sure what is the purpose of SELECT statement here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

pbguy
Constraint Violating Yak Guru

India
319 Posts
Posted - 06/14/2007 :  01:26:35  Show Profile  Reply with Quote
Is view is variable? if so then declare as

instead of case use
If @views is null
Set @view = 1

if it is column then put the FROM clause

still problem..tell us with sample data and required output.

--------------------------------------------------
S.Ahamed
Go to Top of Page

shawnmolloy
Yak Posting Veteran

USA
93 Posts
Posted - 06/14/2007 :  02:03:15  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
There is no output required. I am just trying to increment the "Views" column of that table. Views is the column name, not a variable.

I dont need the select statement, I just need to check for a null value because some of the columns are null.

Thanks.
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 06/14/2007 :  02:09:16  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Have you tried my solution?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 06/14/2007 :  09:02:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
The CASE statement apparently isn't working.... any advice?
>>

1 Did you get error?
2 Did you get unwanted result?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

shawnmolloy
Yak Posting Veteran

USA
93 Posts
Posted - 06/14/2007 :  14:51:17  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
Hi Harsh.

I tried your solution and it did work great! Thanks again.

For future reference here is the query:

UPDATE theDb_image SET [views] = Coalesce([views], 0) + 1 WHERE imageId=@imageI

It seems that the Coalesce function is perfect for this scenario. I have never used it, but now I have added a powerful new tool to my arsenal.

"Given a number of arguments, the Coalesce function will return the first argument that does not have a value of null. Note that if all of the arguments are null, Coalesce itself will return null!"

Thanks again!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000