CODEWITHSUNDEEP
sql-servert-sql
sagesky36
sagesky36

Reputation: 4692

how to check for a null value in t-sql

I want to check for a value in a table to see if it's null.

If so, I just want output to be '0.000'; otherwise, I'd like the actual value which must be converted to a varchar field.

The definition of the column in the database is decimal(10,3).

When I run my below query (pertinent part of the query), I get an error message:

Error converting data type varchar to numeric.

Here is my t-sql syntax that is getting the error. How do I correct it?

isnull(nullif(Ryder_Price,'0.000'),convert(varchar(max),Ryder_Price))

EDIT

Here is the full SELECT statement:

Select '4'+','
            +','+Case when Country_Code= 1 then 'USA' else 'CAN' End 
            +','+
            +','+isnull(nullif(Ryder_Price,'0.000'),convert(varchar(max),Ryder_Price))
            +','+isnull(nullif(Ryder_Price,'0.000'),convert(varchar(max),Ryder_Price))
            +','+isnull(nullif(Ryder_Price,'0.000'),convert(varchar(max),Ryder_Price))
            +','+
            +','+'01/01/1900'
            +','+'01/01/2099'PartMaster_String
            ,4 ord,part_no 
            , RIGHT('000'+convert(varchar(3),ATA_SYSCode),3)+'-'+RIGHT('000'+convert(varchar(3),ATA_Asmcode),3)+'-'+RIGHT('000'+convert(varchar(3),ATA_Partcode),3) taskstring
            From tbl_CPM_PARTS_MASTER_PTC

Upvotes: 0

Views: 358

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

The reason for the error message is that it is possible for the line you posted to simply have Ryder_Price as a value, which is a decimal type. And then you are trying to concatenate it to a string.

To acheive your stated goal:

I want to check for a value in a table to see if it's null.

If so, I just want output to be '0.000'; otherwise, I'd like the actual value which must be converted to a varchar field.

Try this:

convert(varchar(max),isnull(Ryder_Price,'0.000'))

Upvotes: 2

Related Questions