top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How we can use double quotes in dynamic SQL statements?

+1 vote
330 views
How we can use double quotes in dynamic SQL statements?
posted Oct 3, 2014 by Amit Kumar Pandey

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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)
answer Oct 5, 2014 by Kali Mishra
...