Creating a dynamic sql query

问题:

Please see this SQL code:

DECLARE @MyQuery nvarchar(max)
set @MyQuery = 'SELECT TOP 1 @TranslatedMessageOutput = 
               ' + @LanguageName + ' FROM local_translation WHERE English =
               '+CHAR(39)+CHAR(39)+Convert(nvarchar(50),
               (select English from inserted))
                +CHAR(39)+CHAR(39)+CHAR(39)+
               ' AND [' + @LanguageDateName + '] NOT LIKE ''%1900%'''

If I type abc for English this query works just fine.

But if I type 'abc' for English the query is throwing an error:

Unclosed quotation mark after the character string "". Incorrect syntax near "."

How do I solve this?

回答1:

I suspect you will need to escape the output of this query:

(select English from inserted)

As in

(select replace(English, '''', '''''') from inserted)

The above will replace ' by '' before concatenating the outcome to your dynamic SQL statement. But with any dynamic SQL, I strongly suggest you make use of bind values to prevent such SQL syntax errors and SQL injection! I.e. you should be able to write something like this:

declare @English;
select @English = English from inserted;
set @MyQuery = 'SELECT TOP 1 @TranslatedMessageOutput = 
               ' + @LanguageName + 
               ' FROM local_translation WHERE English = @English'
               ' AND [' + @LanguageDateName + '] NOT LIKE ''%1900%''';
-- ...

回答2:

In Dynamic sql queries in order to avoid confusion ,use Char(39) instead of '. Eg :

SET @T='SELECT '+CHAR(39)+'NAME'+CHAR(39)

returns

SELECT 'NAME'