Help with CASE NULL and CONVERT to numeric

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

Transact-SQL (2005)

Help with CASE NULL and CONVERT to numeric

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-03-05 : 07:17:49

I have the following code that I know doesn't work due to the CASE statement checking for NULL. Can anyone help me modify it to provide the following logic. I can usually check for NULLs okay, but the second part is confusing me...

1) If @array_value is null then use null
2) If @array_value is a blank string then use null
3) Otherwise, convert @array_value to a numeric(5,2) type

My incorrect code is below:


INSERT INTO @tbl_levels 
	(
	tLevel
	) 
VALUES 
	(
	CASE @array_value
		WHEN NULL THEN NULL -- WRONG!
		WHEN '' THEN NULL
	ELSE
		CONVERT(NUMERIC(5,2), @array_value)
	END								
	)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-05 : 07:19:44

[code]INSERT INTO @tbl_levels
(
tLevel
)
VALUES
(
CASE
WHEN @array_value IS NULL THEN NULL
WHEN @array_value = '' THEN NULL
ELSE
CONVERT(NUMERIC(5,2), @array_value)
END
)
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-05 : 07:23:19

The CASE expression needs to check inside.


CASE
    WHEN @array_value IS NULL THEN NULL
    WHEN @array_value = '' THEN NULL
    ELSE CONVERT(NUMERIC(5,2), @array_value)
END

You can't compare to a null so you can't use the simpler CASE <x> WHEN <y> WHEN <z> ....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-05 : 07:29:46

or:
case isnull(@array_value,'')
when '' then null
else convert(...
end

No, you're never too old to Yak'n'Roll if you're too young to die.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-03-05 : 07:45:19

Excellent. I wasn't aware that you could compare values and types in the same Case block (a limitation in .NET)
Thanks for the heads up :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources