Solved

SQL Update IsNull or Coalesce?

Posted on 2014-02-01
5
295 Views
Last Modified: 2014-02-01
I have @Price which returns null some times so I would like to use 'na' or @Reason.
I'm Not having any success so far with IsNull or Coalesce.

Declare @Price            varchar(100)
Declare @Reason            varchar(100)

Set @Price = IsNull(@Price, 'na')
-or-
Set @Price = IsNull(@Price, @Reason)
-or-
Set @Price = Coalesce(@Price, @Reason)

Exec ('Update #Temp Set [' + @LName + ' - ' + @FName + '] = ''' + @Price + '''
           Where ID = 23 ');

I'm using 'Exec' because I have dynamic column names and I cannot get @ColumnName to work, the Update is simply ignored.

Set @ColumnName = '['+ @LName +' - ' + @FName + ']'
Update #Temp Set @ColumnName = IsNull(@Price, null)
Where ID = 23
0
Comment
Question by:WorknHardr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39826497
>I'm Not having any success so far with IsNull or Coalesce.
Your T-SQL looks correct.  Are you sure that there's not an empty value '', instead of NULL?

Set @Price = CASE WHEN ISNULL(@Price, '') = '' THEN 'na' ELSE @Price END

In the above T-SQL, @reason has not been assigned a value, so not sure what you mean by that.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39826527
if  @price is numerical, isnull/coalesce will complain about data type conversion.
so, you need to cast @price to varchar also:
isnull( cast(@price as varchar(100)), 'N/A') 

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39826583
I would assume price is a numeric field, so you really can't set it to "n/a" unless you cast it to varchar as Guy Hengel stated. However, if you need to do any type of math with price (e.g. price * quantity = total_cost), you won't be able to. Perhaps you should just consider that, if the price is null, to be 0 instead, then you could use coalesce(@price,0).
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39826949
I think the @FNAME, @LNAME also containing the nulls, it would be better to add the isnull for them and put a default column name over there.
that way the update might still work
0
 

Author Closing Comment

by:WorknHardr
ID: 39827128
yea it's '' instead of just null, thx
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question