Changing if statement to sql code

• rs80

  SSCrazy

  Points: 2312

  More actions

  May 17, 2017 at 7:58 am

  #342920

  I wanted to change the code in the below if statement to more like sql code.  Can I simplify it even more?  Thanks for the help.
  If statement code:
  
exec @dtEffective = trading..selUpdEnv 'PrevBusDt','=',@cdUser,'N'
  exec @dtCurBus = trading..selUpdEnv 'CurBusDt','=',@cdUser,'N'

    if (@dtsStart = "")
  begin
   select @dtStart = @dtEffective
  end
  else
  begin
   select @dtStart = convert(int,convert(char(08),@dtsStart,112))
  end

  SQL Code:
  
   --Get and Set the Dates
    EXEC @dtEffective = trading..selUpdEnv 'PrevBusDt', '=', @cdUser, 'N'
    EXEC @dtCurBus = trading..selUpdEnv 'CurBusDt', '=', @cdUser, 'N'

      SELECT @dtStart = CASE WHEN @dtsStart = '' THEN COALESCE(NULLIF(@dtsStart, ''), @dtEffective)
                         ELSE convert(int,convert(char(08),@dtsStart,112))
                     END


• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 17, 2017 at 8:14 am

  #1942923

  rs80 - Wednesday, May 17, 2017 7:58 AM

  I wanted to change the code in the below if statement to more like sql code.  Can I simplify it even more?  Thanks for the help.
  If statement code:
  
  exec @dtEffective = trading..selUpdEnv 'PrevBusDt','=',@cdUser,'N'
    exec @dtCurBus = trading..selUpdEnv 'CurBusDt','=',@cdUser,'N'

    if (@dtsStart = "")
  begin
   select @dtStart = @dtEffective
  end
  else
  begin
   select @dtStart = convert(int,convert(char(08),@dtsStart,112))
  end

  SQL Code:
  
   --Get and Set the Dates
    EXEC @dtEffective = trading..selUpdEnv 'PrevBusDt', '=', @cdUser, 'N'
    EXEC @dtCurBus = trading..selUpdEnv 'CurBusDt', '=', @cdUser, 'N'

      SELECT @dtStart = CASE WHEN @dtsStart = '' THEN COALESCE(NULLIF(@dtsStart, ''), @dtEffective)
                         ELSE convert(int,convert(char(08),@dtsStart,112))
                     END


   @dtsStart 
   @dtStart
  Is this deliberate?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• rs80

  SSCrazy

  Points: 2312

  More actions

  May 17, 2017 at 8:18 am

  #1942924

  Yes, it is.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 17, 2017 at 8:24 am

  #1942927

  SELECT @dtStart = CASE WHEN @dtsStart = "" THEN @dtEffective ELSE convert(int,convert(char(08),@dtsStart,112)) END

  What are the datatypes of @dtStart, @dtsStart and @dtEffective

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• rs80

  SSCrazy

  Points: 2312

  More actions

  May 17, 2017 at 8:28 am

  #1942929

  @dtStart -> int
  @dtsStart -> datetime
  @dtEffective -> int

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  May 17, 2017 at 10:25 am

  #1942968

  SELECT @dtStart = ISNULL(CONVERT(INT, CONVERT(CHAR(8), @dtsStart, 112)), @dtEffective)

  There's probably nothing you can do about this, but representing dates as integers is a bad idea.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• rs80

  SSCrazy

  Points: 2312

  More actions

  May 17, 2017 at 11:45 am

  #1942989

  You're right, there's nothing I can do about it.

  Purely from a performance perspective, is the case statement faster compared to the ISNULL and Convert code?

  The code below first converts @dtsStart to int and then check if the converted value is NULL. 

  SELECT @dtStart = ISNULL(CONVERT(INT, CONVERT(CHAR(8), @dtsStart, 112)), @dtEffective)

  Thanks for all of your help.

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  May 18, 2017 at 8:29 am

  #1943111

  rs80 - Wednesday, May 17, 2017 11:45 AM

  You're right, there's nothing I can do about it.

  Purely from a performance perspective, is the case statement faster compared to the ISNULL and Convert code?

  The code below first converts @dtsStart to int and then check if the converted value is NULL. 

  SELECT @dtStart = ISNULL(CONVERT(INT, CONVERT(CHAR(8), @dtsStart, 112)), @dtEffective)

  Thanks for all of your help.

  I don't think that there is a detectable performance difference.  I like the ISNULL because I think the logic is slightly clearer.

  You have to remember that you're performing these calculations on variables.  The expensive parts of queries tend to be reads or sorts.  You aren't doing either here.

  I also know that COALESCE is implemented as an underlying CASE expression, but I'm not sure how ISNULL is implemented.  I did run a test on a table, and didn't find any statistically significant difference in the runtimes of ISNULL and a CASE expression, but ISNULL did seem to be slightly more efficient.  Admittedly, the table may have been too small to make a final determination.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• rs80

  SSCrazy

  Points: 2312

  More actions

  May 18, 2017 at 11:18 am

  #1943157

  Thanks for your help, Drew.