CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Code_Learner
Code_Learner

Reputation: 57

Parse a single quote in parameter which is used in a dynamic SQL statement

I have a stored procedure which accepts a string username. It uses a dynamic SQL statement.

SQL by default converts 'Examp'le' to 'Exam''le' but it is still failing in cast as nvarchar as it should be 'Examp''''le' to work there.

Is there any way to achieve this without string manipulation to replace two quotes with 4.

Below is a sample query of what I am trying to achieve

I want the parameter to have the value Examp'le

'select * from 
tablename where name = ' + '''' + CAST(@parameter AS  nvarchar(254)) + ''''

Upvotes: 1

Views: 445

Answers (2)

droebi
droebi

Reputation: 944

It is better to use sp_executesql, try this:

DECLARE @Parameter NVARCHAR(254) = 'Value xyz...'

EXEC sp_executesql N'SELECT * FROM tablename AS t WHERE t.Name = @1',
N'@1 NVARCHAR(254)',
@Parameter

The advantages are: 1st it's more secure and 2nd you have not the problem with counting quotes ;)

For further information look at: https://blogs.msdn.microsoft.com/turgays/2013/09/17/exec-vs-sp_executesql/

If you want more than one parameter look on my post here: How to optimize a query with multiple OR, AND, IN statements?

Upvotes: 2

Zeina
Zeina

Reputation: 1603

You should replace the single quote with a double quote first. If you don't have a single quote in your parameter, then nothing will be replaced.

SET @Parameter = REPLACE(@Parameter, '''', '''''')

' SELECT * FROM tablename WHERE name = ''' + @parameter + ''' '

Upvotes: 1

Related Questions