Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Passing strings with quotes to sp's as params

    Hi,

    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!

    Thanks v. much.
    Matt.

    Call:
    exec GetDocs @IncludeMigrated=0,@Location=N'\\myserver\design\e sther''s emails'

    SP:
    create proc [dbo].[GetDocs]
    @IncludeMigrated bit,
    @Location nvarchar(404)
    as
    begin

    set nocount on;
    declare @cmd nvarchar(1000)

    set @cmd='select id, docname, docnumber, [path] from dbo.tbltoimport with (nolock) where '

    if @IncludeMigrated=0
    set @cmd = @cmd + ' AND PartNo IS Null'

    if @Location <> '<All>'
    set @cmd = @cmd + ' AND [Path] = ''' + @Location + ''''

    exec sp_executesql @cmd

    end

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Matt

    A few things:

    Please can you wrap your code in [code ] [/code ] tags (just remove the space). This is always important, but ESPECIALLY when piddling about with single quotes as the font is fixed width.

    Second - why are you using dynamic SQL? You can do this with static SQL, eliminating your problem, increasing efficiency, increasing simplicity and not exposing yourself to SQL Injection.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ooh - and finally, only use NOLOCK if it is not important that the data you return is correct. This is rarely the case BTW.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2009
    Posts
    4
    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.

    Anyway.. here is the code:
    Call:
    Code:
    exec GetDocs @IncludeMigrated=0,@Location=N'\\myserver\design\esther''s emails'
    SP:
    Code:
    create proc [dbo].[GetDocs]
    	    @IncludeMigrated bit,
    	    @Location nvarchar(404)
    as
    begin
    
    set nocount on;
    declare @cmd nvarchar(1000)
    
    set @cmd='select id, docname, docnumber, [path] from dbo.tbltoimport with (nolock) where '
    
    if @IncludeMigrated=0
    	set @cmd = @cmd + ' AND PartNo IS Null'
    
    if @Location <> '<All>'
    	set @cmd = @cmd + ' AND [Path] = ''' + @Location + ''''
    
    exec sp_executesql @cmd
    
    end

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ta for the code blocks.

    BTW, there's a difference between two single quotes (that you used, and are correct) and double quotes.

    If you print out the code instead of execute you'll see the problem.

    Easier still to sorting this out is using the parameters arguments of sp_executesql, since that is what they are for.

    Code:
    ALTER PROCEDURE  dbo.GetDocs
            @IncludeMigrated bit,
            @Location nvarchar(404)
    as
    begin
    
    set nocount on;
    declare @cmd nvarchar(1000)
            , @paramdefinition nvarchar(500)
    
    
    
    SELECT    @cmd=    'select id, docname, docnumber, [path] from dbo.tbltoimport with (nolock) where ' +
                '(@IncludeMigrated=1 OR PartNo IS Null) ' +
                'AND (@Location = ''<All>'' OR [Path] = @Location)'
            , @paramdefinition = '@IncludeMigrated bit, @Location nvarchar(404)'
    
    exec sp_executesql    @cmd
                        , @paramdefinition
                        , @IncludeMigrated = @IncludeMigrated
                        , @Location = @Location
    
    end
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2009
    Posts
    4

    You sir are a genius!

    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.

    Cheers,
    Matt.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    As a teeny pointer, the following will cause a scan, which is not good for performance!
    Code:
    (@Location = ''<All>'' OR [Path] = @Location)
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •