How to use single quotes inside sql transaction statement

Question

I want to use single quotes inside a transact sql statement, and then execute that statement.

for example my query:

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

now i want to use like this:

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

this does not work and this error occurred:

Invalid column name '1'

I know that the problem is the quotes in the left and right parts 1

this is a sample and I want to use this method for a large request

of course i want to use a local variable instead, like '1', and my local variable is varchar

any idea?

Answer 1

Just exit the quotes:

change

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

to

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

** Change **

To include a local variable in the result, you can update your query as follows:

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

Answer 2

Double single quotes in quote!

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

Answer 3

Use double ticks to avoid them:

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

If you want to use a local variable, as you mentioned in your comment, you can do this:

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

Answer 4

wrap one single quote to like more ''' and ticks will work too.

Answer 5

The Oracle database allows you to use square brackets instead of double single quotes, such as v = '[qry]'. This is very convenient when you have code with many single quotes. You can simply test the query and copy it into square brackets without worrying about changing single quotes.

Is there a similar option on sql server?

Answer 6

Try using double quotes instead:

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