apostrophe in variable value in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

apostrophe in variable value in stored procedure

Author

Topic

mlsrinivas
Starting Member

3 Posts

Posted - 2011-03-08 : 02:42:57

Hi,

I have a SP that returns a select statement. This SP will take 2 input variables which are used in where condition of the select statement. I am having problem when the variable values are having apostrophe (') in them.

As the variable values are coming from front end, I am sending '123123' in place of apostrophe and replacing them with double apostrophe before querying.

set @candname=replace(@candname,'123123', '''''');

so, If I send @candname as D'Souza (D123123Souza), it will become as D''Souza before being used in select statement.

select * from employees where empname=@candname;
(this did not work, working fine with names with out apostrophe)

I tried with
select * from employees where empname=''' + @empname + ''';

Still it did not work. I could make it work with

set @sql='select * from employees where empname=''' + @empname + '''';

exec (@sql);

But I want it like
select * from employees where empname=''' + @empname + ''';

Please help.

Thanks in advance.

Srinivas

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 03:16:44

Try:
set @candname=replace(@candname,'123123', '''');

select * from employees where empname= @empname ;