SQL Server 2005 Stored procedure problem contatenating string with wildcard

Question

I have a lengthy stored procedure that builds a query string. It works fine until I add in a 'LIKE' description field (text) which has a wildcard in it, see below:

IF @AdDescription IS NOT NULL   
    IF @AdSection IS NOT NULL
        BEGIN
            SET @SQL = @SQL + @Wand + 'na.Section = '' + @AdDescription + '''
            SET @Wand = ' AND '
        END
    ELSE
        BEGIN
            SET @SQL = @SQL + @Wand +  '(na.AdDesc LIKE ''' + @AdDescription + '%'')'
            SET @Wand = ' AND '
        END

I've tried a few variations but as soon as @AdDescription has anything in it it fails. Is there something obvious that I am missing?

Answer 1

Try using print to see the SQl you have created, then you will likely see the error. And try really hard to avoid dynamic sql, it's hard to maintain, test, and debug properly.

Instead of exec the SQl just have it do:

Print @SQL

This will print the SQL Statement.

We usually havea debug parameter on any stored proc that uses dynamic SQl abd if it is set to 1, it prints the SQL and if it is set to 0 (the default), it executes the SQL. This makes it easier to see what is being created for a set of values later when the proc fails in production becasue of some obscure condition you didn't consider.

YOu can also get the SQl executed by running profiler, but usually it's simpler to just run with the same values in debug mode.

Answer 2

You are missing an apostrophe in your first "SET @SQL" line. There should be 3 apostrophes before you add the @AdDescription. The color coding in your code above shows this problem. The plus signs are red instead of black.

Answer 3

Edit Maybe it's just the quotes? I've added some spaces so the escaping is clearer.

IF @AdDescription IS NOT NULL
    IF @AdSection IS NOT NULL
        BEGIN
            SET @SQL = @SQL + @Wand 
                 + 'na.Section = ' ''' + @AdDescription + ''' '
            SET @Wand = ' AND '
        END
    ELSE
        BEGIN
             SET @SQL = @SQL + @Wand 
                 +  '(na.AdDesc LIKE ' ''' + @AdDescription + '%'' )'
        END