Problem with NULL columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with NULL columns

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-06-14 : 01:21:50

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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-14 : 01:26:34

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"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-14 : 01:26:35

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

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-06-14 : 02:03:15

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.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-14 : 02:09:16

Have you tried my solution?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-14 : 09:02:27

<<
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

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-06-14 : 14:51:17

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources