ISNULL function

• Deepthy

  SSCommitted

  Points: 1823

  More actions

  April 11, 2012 at 5:18 am

  #259743

  Hi all,

  CREATE TABLE TESTTABLE(VersionId int, EffectiveDate DateTime)

  There are no records in this table currently and I want the below variable @test-2 to return getdate() if there is no version whose id is 2167.

  How can I acheive this. The below sql is not helping.

  declare @test-2 datetime

  SELECT @test-2 = ISNULL(TT.EffectiveDate, GETDATE())

  FROM testtable AS TT

  WHERE TT.VersionID = 2167

  select @test-2

  Any help is really appreciated.

  Thanks,

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  April 11, 2012 at 5:26 am

  #1471766

  declare @test-2 datetime = getdate()

  SELECT @test-2 = ISNULL(TT.EffectiveDate, GETDATE())

  FROM testtable1 AS TT

  WHERE TT.VersionID = 2167

  select @test-2

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 11, 2012 at 5:27 am

  #1471767

  Plenty of ways. . .

  Here's one: -

  DECLARE @test-2 DATETIME;

  SELECT @test-2 = ISNULL(b.EffectiveDate,a.EffectiveDate)

  FROM (VALUES (GETDATE())) a(EffectiveDate)

  OUTER APPLY (SELECT *

  FROM testtable

  WHERE VersionID = 2167) b;

  SELECT @test-2;

  And another: -

  DECLARE @test-2 DATETIME;

  SELECT @test-2 = EffectiveDate

  FROM testtable

  WHERE VersionID = 2167;

  SET @test-2 = ISNULL(@test,GETDATE());

  SELECT @test-2;

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Deepthy

  SSCommitted

  Points: 1823

  More actions

  April 11, 2012 at 5:32 am

  #1471769

  Thanks Suresh. That helped

• vinu512

  SSCoach

  Points: 15729

  More actions

  April 12, 2012 at 12:54 am

  #1472400

  This might be another way....do correct me if I am wrong.

  Declare @test-2 datetime

  If Not Exists(Select ID From Test Where Id = 219798)

  Begin

  Set @test-2 = getdate()

  End

  Select @test-2

  Hope this helps.

  Vinu Vijayan

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

• Dwain Camps

  SSC Guru

  Points: 86978

  More actions

  April 12, 2012 at 1:33 am

  #1472407

  Agreed. Many ways. Here's another.

  DECLARE @TESTTABLE TABLE (VersionId int, EffectiveDate DateTime)

  declare @test-2 datetime

  SET @test-2 = ISNULL((

  SELECT TOP 1 TT.EffectiveDate

  FROM @testtable AS TT

  WHERE TT.VersionID = 2167), GETDATE())

  select @test-2

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St