How to use isnumeric with case when inserting to a table

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

Transact-SQL (2000)

How to use isnumeric with case when inserting to a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-15 : 09:12:25

sandra marone writes "Fetching a varchar(6) column (can contain alpha or numeric value) into @MAOT varchar(6) variable. Using the insert statment, with case, using ISNUMERIC (variable name) to copy the numeric value to the Int 4 column, ELSE copy null to int 4 column. This code gives me no error, inserts null to column when @MAOT is numeric. How do I do this? Part of insert below:

CASE @MAOT
WHEN ISNUMERIC(@MAOT) THEN CAST(@MAOT AS INT)
ELSE NULL probably need a cast or conv here too ?
END

Really appreciate Help on this one, thanks, Sandy."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-15 : 09:30:55


CASE
   WHEN ISNUMERIC(@MAOT) = 1 THEN CAST(@MAOT AS INT)
   ELSE NULL
END

You want the 'searched CASE' rather than 'simple CASE' syntax.*
ISNUMERIC returns an int, and ints cannot be used as boolean test conditions.

*Sorry, that's a little more prescriptive than necessary: the 'simple CASE' syntax can be used too, but it looks a little odd to my eye:


CASE ISNUMERIC(@MAOT)
   WHEN 1 THEN CAST(@MAOT AS INT)
   ELSE NULL
END

Edited by - Arnold Fribble on 08/15/2002 09:36:50

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 09:41:07

Mine too. The first one is more clear.

r937
Posting Yak Master

112 Posts

Posted - 2002-08-15 : 12:05:27

and it wouldn't hurt to write

... ELSE CAST(NULL AS INT)

sql/server may do an implicit conversion, but i have plenty o' scars and prefer to take no chances around NULLs, just a habit that allows me not to have to stop and think about implicit conversions

rudy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources