Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    19

    Unanswered: String Concatenation

    Hi,

    Why does this result produce 'Null' and not the expected string of 10 B's?

    The var @SecurityString is a VarChar Type.

    WHILE @LoopCount <= 10

    BEGIN
    SET @SecurityString = @SecurityString + 'B'
    SET @LoopCount = @LoopCount + 1
    END
    SELECT @SecurityString AS SecurityCode

    Any pointers would be a great help, thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when a variable is defined, but its value is not set explicitly, then its value is NULL

    and of course, concatenating anything to NULL produces NULL

    try putting this in front of the WHILE statement --
    Code:
    SET @SecurityString = ''
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    19
    Thanks a lots for that!

    Amzaing how something so small as creating an empty string can cause such a stopper!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It can get even more confusing.

    The behavior of NULL concatenations is actually a connection setting. By default, NULL concatenations yield NULLs, but that can be changed at runtime by using a different connection option. It so happens that Crystal Reports (at least earlier versions...) used a default connection setting that allowed concatenation of NULL values. I can't even remember how many hours I spend trying to figure out why the hell my simple code would return one set of values in Query Analyzer and a completely different result in Crystal Reports!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •