Unanswered: Passing strings with quotes to sp's as params
I have a stored proc which works fine except where one of the passed in values contains a quote. In the calling application I have escaped single quotes with double quotes already, so the call looks OK to me, but it seems I actually need four quotes to get it to work!
The application is now being sunset, so I don't want to roll out a new version of the app, so is there a way of handling these single quotes at the stored proc end. I can't see why it isn't working as it is properly escaped anyway!
The code in the stored proc is actually much more complicated than that shown, there is some logic used to determine what should be queried based on params that are not shown, so the dynamic part is I think necessary - there's no problem with that part of the code, so I didn't show it.
Nolock is in this instance OK because the part of the row being read is not for edit except by an overnight datawarehouse refresh. It's not best practice I know, but I am actually compensating for some bad app design :-(. I'll post the whole stored proc if you think it's relevant, just trying to keep it simple.
create proc [dbo].[GetDocs]
set nocount on;
declare @cmd nvarchar(1000)
set @cmd='select id, docname, docnumber, [path] from dbo.tbltoimport with (nolock) where '
set @cmd = @cmd + ' AND PartNo IS Null'
if @Location <> '<All>'
set @cmd = @cmd + ' AND [Path] = ''' + @Location + ''''
exec sp_executesql @cmd
Thanks - just got round to testing it and that works beautifully. I followed through on the original logic and worked out that the problem was with sending the sql command to sp_executesql, thanks for that pointer as well.