Post reply

Changing if statement to sql code

  • 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

  • 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

  • May 17, 2017 at 8:18 am

    #1942924

    Yes, it is.

  • 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

  • May 17, 2017 at 8:28 am

    #1942929

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

  • 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

  • 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.

  • 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

  • May 18, 2017 at 11:18 am

    #1943157

    Thanks for your help, Drew.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply