how to get 0 if records have empty or null values when column datatype is numeri

Question

how to get 0 if records have empty or null values when column datatype is numeri

asrinu13

Say Hey Kid

Points: 709
More actions
July 23, 2019 at 9:35 am

#3665312

Hi I have one doubt in sql server
how to get 0 when records have empty or null values when column datatype is numeric in sql server else get max(id) values in sql server
Table : empid
CREATE TABLE [dbo].[empid](
[id] [numeric](11, 0) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[empid] ([id]) VALUES (NULL)
GO
INSERT [dbo].[empid] ([id]) VALUES (CAST(6 AS Numeric(11, 0)))
GO
based on above data I want output like below
id
6
I tried like below
select case when isnull( id,'')='' then cast (0 as numeric) else id end test from
[Test].[dbo].[empid]
but above query is getting error
Msg 8114, Level 16, State 5, Line 9
Error converting data type varchar to numeric.
suppose no records then id is 0
please tell me how to write a query to achive this task in sql server

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

You need to learn about data types. An expression in SQL Server has a static data type. CASE is an expression. And thus, a CASE always return the one and same data type no matter what you mix. If you mix types in your THEN branches, SQL Server applies a type-precedence list, so the type with the highest precedence wins, and all other values will be converted to that type - if an implicit conversion exists at all.

When it comes to isnull, the type of isnull is always the type of the first argument. You are saying that isnul(id, ''), but '' is not a legal decimal value. SQL Server attempts to convert the string to decimal, but this fails. (Curiously, it would have succeeded if id would have been int.)

But why play with isnull at all? Why not just say: WHEN id IS NULL THEN 0?

Or you can use coalesce: coalesce: coalesce(id, 0). coalesce is deceivingly similar to isnull, but it is a shortcut for

CASE WHEN is NULL THEN 0 ELSE id END

And in difference to isnull, you can have more than two arguments:

coalesce(val1, val2, .... valN)

It is still a shortcut for a log CASE that returns the first non-NULL value.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 2

your issue is ISNULL and implicit conversions.

ISNULL(NumericColumn,'') fails because numeric column can never be empty string (the value of '')

the isnull function is attempting to cast the varchar value of empty string to numeric, and that implicit conversion is invalid.

integer values DO have an implicit conversion of empty string to zero,which can cause some confusion.

in that example,

the second parameter of ISNULL should be the desired data type and value:

SELECT 
ISNULL(numericColumn,0.00),
ISNULL(FirstName,''),
ISNULL(StatusID,2),
isNull(StatusDescription('InProcess') ,
ISNULL(CreatedDate,getdate()) 
FROM SomeTable

This reply was modified 5 years, 5 months ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mohan.muralid SSC Journeyman Points: 99 More actions · Answer 3

Hi,

Please find the query below:-

select ISNULL([id],0) id from empid 

OR

select case when ISNULL(id,0) = 0 then 0 else [id] end as enum from empid