?
Solved

Passing nested quotes in string as parameter to stored procedure

Posted on 2007-08-09
7
Medium Priority
?
496 Views
Last Modified: 2010-03-19
I am using a stored procedure that implements a generalize paging control. The first parameter of the SP is the SELECT statement.

I want to create a paged version of a query like this one:
SELECT OrderNumber FROM Orders WHERE Sku LIKE '%Sample%' ORDER BY OrderNumber

The trouble is when I rewrite it for the stored procedure I can't use the single quotes around the '%Sample%' because the SP expects the first parameter to be a string itself.

exec PageQuery
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku LIKE %Sample%',
@OrderBy = 'OrderNumber DESC',
@PageNum = @pPageNum,
@PageSize = 18

results in
Exception=Incorrect syntax near 'Sample'

exec PageQuery
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku LIKE '%Sample%'',
@OrderBy = 'OrderNumber DESC',
@PageNum = @pPageNum,
@PageSize = 18

(two pairs of nested single quotes) results in
Exception=Incorrect syntax near '%'.

How do I get the proper quoting without rewriting the SP? I've tried concatenating with &, wrapping with () and a few other tricks to no avail. Can this be done without modifying the SP? It's part of a third party package and other code relies on it so I'm reluctant to change it.

I have a similar problem when I want to include a variable as part of the select:
exec PageQuery
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku = @Sku',
@OrderBy = 'OrderNumber DESC',
@PageNum = @pPageNum,
@PageSize = 18
where I want the @Sku variable expanded first then passed to the SP. Similar solutions?

thanks!
0
Comment
Question by:jasonrfox
7 Comments
 

Author Comment

by:jasonrfox
ID: 19666718
After a little more searching and experimenting it seems that doubling up the single quotes may do the trick.

exec PageQuery
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku LIKE ''%Sample%''',
@OrderBy = 'OrderNumber DESC',
@PageNum = @pPageNum,
@PageSize = 18

two single quotes before %Sample% and three after. So is the lesson where one quote isn't enough, just add more?

I'd like this syntax verified by someone, if possible, and I don't think it solves the second case with a variable.
0
 
LVL 39

Expert Comment

by:appari
ID: 19666992

yes you have to double each of the single quotes. actual query executed by sqlserver is one single quote only.
can you explain the second problem.
0
 
LVL 3

Expert Comment

by:Minna
ID: 19667225
First problem -
exec PageQuery
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku LIKE ''%Sample%''',
@OrderBy = 'OrderNumber DESC',
@PageNum = @pPageNum,
@PageSize = 18


Second problem -
exec PageQuery
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku = ' + @Sku,

or if @Sku is a string
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku = ''' + @Sku + '''',

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Expert Comment

by:Miriam0000
ID: 19667410
In general in SQL...

'        marks begining of string value
''       is used to represent an ' in the middle of a string
'''      is either begining of a string whose first char is '  or the end of a string whose last char is a '
0
 

Author Comment

by:jasonrfox
ID: 19670911
The descriptions of single quotes above match the behavior of solving the first case. I'm quite familiar with escaping characters, but backslash is more conventional in the programming languages I use. In the second case the extra quotes don't seem to help:

exec PageQuery
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku = ' + @Sku,

or if @Sku is a string
@Select = 'SELECT OrderNumber FROM Orders WHERE Sku = ''' + @Sku + '''',

both result in
Exception=Incorrect syntax near '+'.

@Sku happens to be a varchar in my example, but could be anything for me in general. I've seen usage of CONVERT in some situations. I wonder if that would help here? The error is about syntax though...
0
 
LVL 3

Accepted Solution

by:
Minna earned 2000 total points
ID: 19690013
Okaaay! I see what you're saying now.
It would work if you created the string first and not build it into the parameter

eg
DECLARE @selectStatement varchar(2000)
SELECT @selectStatement = 'SELECT OrderNumber FROM Orders WHERE Sku = ''' + @Sku + ''''

exec PageQuery
@Select = @selectStatement,
etc
0
 

Author Comment

by:jasonrfox
ID: 19801869
I'll try that
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question