Post reply

Purpose of setting variable to empty string?

  • October 31, 2013 at 10:13 am

    #285467

    Hi,

    I frequently see code like

    SELECT @SQL = ''

    SELECT @SQL = COALESCE(@SQL,'')

    And then on the following line the actual t-sql is concatenated to the empty string. What is the purpose of that? Is it just to clear out anything that might have been lurking in previous code? Does it help with parameter sniffing? Are there times when it shouldn't be used?

    Thanks

  • October 31, 2013 at 10:18 am

    #1663162

    Those two are redundant, you don't need both, but you do want to set the variable to something before concatenating. Otherwise it's NULL and NULL + Anything = NULL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 31, 2013 at 10:23 am

    #1663168

    GilaMonster (10/31/2013)

    Those two are redundant, you don't need both, but you do want to set the variable to something before concatenating. Otherwise it's NULL and NULL + Anything = NULL.

    Sorry, I didn't mean I saw them together, I was just giving examples of the way I've seen it done. The first is the most common, the second I've seen Mr. Moden do in some of his articles that contain dynamic SQL.

    Thank you though, that certainly makes sense. I guess I was reading too much into it.

Viewing 3 posts - 1 through 2 (of 2 total)

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