October 3, 2007 at 5:19 pm
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
Have a look at QUOTENAME:
QUOTENAME ( 'character_string' [ , 'quote_character' ] )
October 3, 2007 at 5:41 pm
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
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