CASE statement or UDF?

• Sam-241854

  SSC Enthusiast

  Points: 138

  More actions

  February 28, 2006 at 9:28 pm

  #113597

  So I've got a legacy database..

  There are a number of INT columns in it which may have values of -32768 which is supposed to represent a NULL value.

  I need to return these -32768's as actual NULL's in a T-SQL query (updating the data in the DB is not an option).

  Currently the query is like so:

  SELECT

  CASE WHEN col1 = -32768 THEN NULL

  ELSE col1 END

  FROM tbl1

  My question is, would I get a performance benefit by creating a UDF to filter out the -32768 values and using it like so:

  SELECT

  myUdf(col1)

  FROM tbl1

  ?

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  February 28, 2006 at 9:47 pm

  #623856

  There is NULLIF system function for your service.

  See BOL for details.

  _____________
  Code for TallyGenerator

• Sam-241854

  SSC Enthusiast

  Points: 138

  More actions

  February 28, 2006 at 9:58 pm

  #623857

  Ah - thanks.

• AMIT GUPTA-263376

  SSCarpal Tunnel

  Points: 4244

  More actions

  March 1, 2006 at 4:34 am

  #623907

  HI,

  use case in your update query.It is better rather to create a function because you can't use the update statement in your functions

   

  Regards

  Amit