Handling single quotes in SQL Server

In SQL Server, sometimes we will have single quotes in the values and sometimes we need to fetch the values from the tables. Seems to be interesting rights... Let see how can we achieve it,

Considering for example, I want to fetch the name Michael's. In that case, we can write

select * from table where name='michael's' -- More meaning ful

But, SQL Server won't recognise the above query and it will throw an error showing incorrect syntax because, SQL Server will recognise upto the letter "L" after that it won't recognise as a proper SQL Sentence.
select * from table where name='michael'

To over come this problem, SQL Server is providing a handy approach by putting two consecutive single quotes instead of one single quote. So the query should be,

select * from table where name='michael''s' --Query won't show any error and it will server the purpose.

Let see a small sample on this,


create table venkat(id int ,name varchar(100))
insert into venkat values(1,'venkat')
insert into venkat values(2,'venkat1')
insert into venkat values(3,'venkat2')
insert into venkat values(4,'venkat3')
insert into venkat values(5,'venkat4')
insert into venkat values(6,'venkat5')
insert into venkat values(6,'venkat''5')
select * from venkat

create procedure Venkat_sample
(@nam varchar(100))
as
begin
select * from venkat where name=@nam
end
exec venkat_sample 'venkat''5'



Thanks and Regards,
Venkatesan Prabu .J