Handling Apostrophe in Dynamic SQL

It’s always difficult to handle Apostrophe in SQL queries and when its dynamic query then it’s become too difficult.

Suppose you want to update some table using dynamic query and the parameters passed to the query may contains apostrophe then it is difficult to handle it.

One way is to replace single apostrophe by double one like 

set @varname = Replace(@varname,'''','''''')

But the most efficient way is to use parameterized dynamic queries like below one

declare @name varchar(100)

set @name=''

declare @address varchar(200)

set @address=''

select @name = testname,@address=testaddress from test where id=1

declare @query nvarchar(max)

set @query = 'update test1 set testname=@name,testaddress=@address'

exec sp_executesql @query,N'@name varchar(100),@address varchar(200)',@name=@name,@address=@address