Case : If you have developed a dynamic search by constructing a sql statement according to user inputs captured through several input controls in your application and one or more of those controls allow single quote as a part of the input string, your dynamically built sql statement will break in to several parts as a result of improperly formatted quote strings in the statement. The simplest solution is to replace the quote(') string with 2 quote strings('').
declare @find nvarchar(5);
declare @replace nvarchar(5);
declare @text nvarchar(100);
set @find = char(39);
set @replace = char(39)+char(39);
set @text = 'nalaka'+char(39)+'s';
set @text = replace(@text, @find, @replace)
set @text = 'select ''' + @text + ''' where getdate() = getdate()'
exec(@text)
declare @find nvarchar(5);
declare @replace nvarchar(5);
declare @text nvarchar(100);
set @find = char(39);
set @replace = char(39)+char(39);
set @text = 'nalaka'+char(39)+'s';
set @text = replace(@text, @find, @replace)
set @text = 'select ''' + @text + ''' where getdate() = getdate()'
exec(@text)
