hamed aj hamed aj - 3 months ago 10
SQL Question

how to use single quotations inside a transact sql statement

i want use single quotations inside a transact sql statement, then execute that statement.

for example my query is:

Select * FROM MyTable WHERE MyTable.Id = '1'


now i want use like this:

Declare @SQLQuery AS NVarchar(4000)
SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = '1' '
Execute (@SQLQuery)


this not work, and this error occurred :


Invalid column name '1'


I know problem is quotations in left and right side of the 1

this is a sample and i want use of this way to a large query

of course, i want use local variable instead for example '1' and my local variable is varchar

any idea?

Answer

Just escape the quotes:

change

SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = '1' '

to

SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = ''1'' '

** Edit **

To include a local variable in the result, you could updated your query like this:

DECLARE @SQLQuery varchar(200)
DECLARE @tmpInt int

SET @tmpInt = 2
SET @SQLQuery = ' Select * FROM MyTable WHERE MyTable.Id = ' + 
     convert(varchar, @tmpInt) + ' '