Ain't SQL great? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Ain't SQL great?

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-15 : 11:11:24

SET @Parm = IsNull(@Parm, 0)

is great. I need the opposite. Change an empty string to NULL, or sometimes change a 0 to NULL.

It's a shame to resort to a conditional IF statement to do the reverse (convert zero values to NULL), the shortest non-IF equivalent espression I can come up with is:

SET @Parm = CASE WHEN @Parm = 0 THEN NULL ELSE @Parm END

Still not pretty. Anyone know a shorter method or function?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-15 : 11:17:08

maybe:
select nullif(@Parm, 0)

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-15 : 11:21:35

hehehe... he should have none better...

Do Not Mock SQL

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-15 : 11:28:03

EDIT: Dooh...NULLIF

Make your own?

CREATE FUNCTION IsZero (@x int)
RETURNS int
AS
BEGIN
RETURN CASE WHEN @x = 0 THEN NULL ELSE @x END
END

DECLARE @parm int
SELECT @parm = 0
SELECT dbo.IsZero(@Parm)
SELECT @parm = 1
SELECT dbo.IsZero(@Parm)

Brett

8-)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-02-15 : 11:45:06

Don't worry Brett, he'll be as old as you and me one day too

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-15 : 11:49:37

who, me??

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-15 : 12:06:14

Gack!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources