• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

SQL Update IsNull or Coalesce?

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
WorknHardr
Asked:
WorknHardr
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
awking00Commented:
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
 
Surendra NathTechnology LeadCommented:
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
 
WorknHardrAuthor Commented:
yea it's '' instead of just null, thx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now