Post reply

' in the stored procedure parameter

  • October 3, 2007 at 5:19 pm

    #198700

    In the reporting services user enters name parameter for search in the text box. If this parameter has ', stored procedure gives an error. How can I replace it in stored procedure to avoid error and find names with ' like O'Neil for example?

    ALTER PROCEDURE [dbo].[NAME_SEARCH]

    @last_name varchar(100)

    AS

    BEGIN

    SELECT REPLACE(last_name, ''', ''''') -- how can I replace ' here?

    SELECT @sql = 'SELECT * from Names where last name = LIKE ''%' + @last_name + '%''

  • October 3, 2007 at 5:36 pm

    #738874

    Have a look at QUOTENAME:

    QUOTENAME ( 'character_string' [ , 'quote_character' ] )

  • October 3, 2007 at 5:41 pm

    #738879

    I just fixed it. I found an answer here http://www.sqlservercentral.com/articles/Advanced+Querying/tamethosestringspart9/637/

    SELECT @last_name = REPLACE( @last_name, '''', '''''')

  • October 3, 2007 at 5:47 pm

    #738881

    Better solution :

    SELECT ColsList FROM dbo.TableName WHERE ColName LIKE '%' + @Param + '%'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply