Dynamic query with variables: dealing with single quotes

I am building a stored procedure, which depending on the values of the inputted parameters, will modify the selection query.

If the value of @x is blank, then the query should look like this:
SELECT * FROM table

If the value of @x is anything else (in this example: 'Value') the query should look like this:
SELECT * FROM table WHERE column = 'Value'

--------------------------------------------------------------

However, if I use single quotes to build the dynamic query, it doesn't recognize the single quotes as part of the string: it thinks that I am closing the line.  

I can get the @SQL string to build correctly, with the exception of the single quotes.  In my code below, I used double quotes "  as a substitute so that I don't get an error.  

SELECT * FROM table WHERE column = "Value"

At this point, how can I replace the """ (double quotes) with "'" (single quotes) in the @SQL variable?  

The idea would be similar to the REAPLACE() function:

REPLACE(@SQL, ", ')  --Find and replace all instances of " with '.  

Is there a better approach for building a dynamic query, while handling single quotes?
DECLARE @x varchar(10)
SET @x = 'Value'
 
DECLARE @SQL varchar (max)
 
SET @SQL = 
'SELECT * FROM table '   --Single quotes are used to assign the string of text to the variable @SQL
 
IF @x != ''  --Add on by appending the optional WHERE clause if x is not blank.
BEGIN  
	SET @SQL = @SQL + 'WHERE column = "'  --I want to replace the double quote: " with a single quote: '
	SET @SQL = @SQL + @x
	SET @SQL = @SQL + '"'   --I want to replace the double quote: " with a single quote: '
END
 
 
PRINT @SQL  --To see the results of the dynamic SQL query.

Open in new window

kyledmorganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
Use sp_executesql and pass the variable
DECLARE @x varchar(10)
SET @x = 'Value'
 
DECLARE @SQL nvarchar (max)
 
SET @SQL = 'SELECT * FROM table '   --Single quotes are used to assign the string of text to the variable @SQL
 
IF @x != ''  --Add on by appending the optional WHERE clause if x is not blank.
BEGIN  
        SET @SQL = @SQL + 'WHERE column = @x '  --I want to replace the double quote: " with a single quote: '
END
 
 
 
PRINT @SQL  --To see the results of the dynamic SQL query.
exec sp_executesql @SQL, N'@x varchar(10)',@x=@x

Open in new window

0
pcelbaCommented:
You may use single quote in a string but you have to dulicate it:

declare @cmd char(1)
SET @cmd = '''' -- assigns one single quote to the @cmd
DECLARE @x varchar(10)
SET @x = 'Value'
 
DECLARE @SQL varchar (max)
 
SET @SQL = 
'SELECT * FROM table '   --Single quotes are used to assign the string of text to the variable @SQL
 
IF @x != ''  --Add on by appending the optional WHERE clause if x is not blank.
BEGIN  
	SET @SQL = @SQL + 'WHERE column = '''
	SET @SQL = @SQL + @x
	SET @SQL = @SQL + ''''
END
 
 
PRINT @SQL  --To see the results of the dynamic SQL query.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kyledmorganAuthor Commented:
Thanks for the quick response: duplicating the single quote handled it correctly.  I would never have guessed that four quotes would add the one quote to the string:

SET @SQL = @SQL + ''''
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

pcelbaCommented:
You are welcome! Complex things have simple solution. Sometimes.
0
BrandonGalderisiCommented:
kyledmorgan:

The proper way to handle this is the method I showed in http:#a24455224 .  I will not require escaping the single quotes.  Additionally, if the value of @x contains a quote, pcelba's solution will not work.
0
pcelbaCommented:
Kyledmorgan did not ask to execute this query but to build it only. It could even mean the string is returned to the calling program or passed to some procedure and in such case the @x variable is not known...

From the other side I would not recommend to use sp_executesql but I would execute the SELECT statement directly:

IF @x = ''
  SELECT * FROM table
ELSE
  SELECT * FROM table WHERE column = @x

Of course, we don't know how much the question was simplified.

But you are right with the single quote in the @x. If such possibility exists then it is necessary to duplicate each single quote in @x by REPLACE()...
0
kyledmorganAuthor Commented:
The example was simplified for the purpose of demonstrating the need to handle the single quotes.  The value of @x is passed in using an external program and will always consist of a foreign key number (int), so I am not concerned about it ever containing a quote.  

Thank you both for your input.  I will consider each of your suggestions in the future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PC

From novice to tech pro — start learning today.