How would T-SQL interpret this coding error?

  • I am debugging some code and have tracked down the line of code that is causing the problems. The following is a direct cut and paste of the dodgy line:

    set @SqlFilter = @SqlFilter + '''' + + dbo.fnDoubleQuotes(@MatchFieldValue)

    As you can see, I have + + in the middle of this code, instead of just +

    I have two questions for the community:

    a) Why does this not generate an error when I first wrote my CREATE PROCEDURE script?

    b) How does T-SQL interpret this line of code?

    I am particularly interested in the answer to (b), as the knock-on effects of this coding error are quite subtle and difficult to understand.

    Going forward, the correction to the code is simple, but I need to know what damage this error may have done in the past, to know if anything else needs fixing.

  • MarkThornton (7/11/2008)


    I am debugging some code and have tracked down the line of code that is causing the problems. The following is a direct cut and paste of the dodgy line:

    set @SqlFilter = @SqlFilter + '''' + + dbo.fnDoubleQuotes(@MatchFieldValue)

    As you can see, I have + + in the middle of this code, instead of just +

    I have two questions for the community:

    a) Why does this not generate an error when I first wrote my CREATE PROCEDURE script?

    b) How does T-SQL interpret this line of code?

    I am particularly interested in the answer to (b), as the knock-on effects of this coding error are quite subtle and difficult to understand.

    Going forward, the correction to the code is simple, but I need to know what damage this error may have done in the past, to know if anything else needs fixing.

  • Himm, it only happens on assignments:

    declare @sqlfilter varchar(33)

    select @SqlFilter = 'a' + 'd' + + 'b'

    Acts like the extra "+" is not there.

    But

    select 'a' + 'd' + + 'b'

    Gives an error.

    I think that it's a T-SQL bug. You should report it to Microsoft.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • MarkThornton (7/11/2008)


    set @SqlFilter = @SqlFilter + '''' + + dbo.fnDoubleQuotes(@MatchFieldValue)

    I think this expression works like this:

    set @SqlFilter = @SqlFilter + '''' + '' + dbo.fnDoubleQuotes(@MatchFieldValue)

    It means if there are two consecutive + then [''] will automatically take place during compilation. If its not the case then its definately a bug.

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

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