It is a common knowledge that if a query contains a double quote, it throws an error but if it contains a single quote, the statement is executed.
For example, the following statement will cause error
select * from customers where city="bbsr"
However, the single quote can be used in a SQL query .
select * from customers where city='bbsr'
You can also use two single quotes in place of one, it is taken as a single quote. You can further concatenate and build a dynamic SQL query as demonstrated below.
declare @city varchar(30)
declare @cn varchar(100)
set @city = 'bbsr'
set @cn = 'Jnana'
declare @query varchar(max)
set @query = 'select *from customers where city = ''' + @city + ''' and companyname = ''' + @cn + ''''
execute(@query)
While this approach may initially serve the purpose, it becomes dificult as you add more conditions to the query.
The best way to do it would be including the following statement in the query
SET QUOTED_IDENTIFIER Off (Use double quote.)
set @query = "select *from customers where city = '" + @city + "' and companyname = '" + @cn + "'"
exec(@query)
