SQL Server changed my strings before running the query

Question

I try to select some special records contain special characters but SQL Server changes my string characters before it running the query.

For example:

    DECLARE @param NVARCHAR(30)

    SET @param=N'¤÷þ'--this is my special string that i want to be searched exactly.

    DECLARE @TSQL varchar(8000)
    SET @TSQL = 'SELECT * FROM MyTable WHERE MyFieldName LIKE %' + @param + '% '

    PRINT @TSQL

    --EXECUTE (@TSQL)

But in the result(print) I see:

SELECT * FROM MyTable WHERE MyFieldName LIKE '%¤÷þ?%'

As you see some part of string converted to (?) character, this problem cause my SELECT command return null value.

I try to change collation of the database that I run the query to:

SQL_Latin1_General_CP1_CI_AS

It work fine with some special string but it also does not support all of my strings. So, question is here: how can I tell SQL Server, please don't change my string ascii codes? Is there any way (or any collation) to say SQL Server that see an string exactly as it is in reality?

PS: I am using SQL Server 2008 R2.

Answer 1

If you have special characters that need to be preserved, use Unicode strings of type NVARCHAR instead of VARCHAR - it's that simple .....

DECLARE @param NVARCHAR(30)
SET @param = N'¤÷þ'--this is my special string that i want to be searched exactly.

DECLARE @TSQL NVARCHAR(4000)    -- <=== use NVARCHAR here
SET @TSQL = N'SELECT * FROM MyTable WHERE MyFieldName LIKE %' + @param + N'% '

PRINT @TSQL

Then your special characters will be preserved as entered ....

And as others have pointed out: concatenating together your SQL statements like this is never a good idea - it opens up your code to potential SQL injection attacks. You should use parameterized queries and sp_executesql which allows you to define and supply paramters to your queries.

Answer 2

DECLARE @TSQL varchar(8000)

varchar(8000) cannot represent ¤÷þ. Just keep doing what you're doing with @param; use something NVARCHAR based.

As usr correctly points out, you should really be using sp_executesql and its ability to specify parameters. From the documentation:

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2012.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;