why we use multiple single quotes in dynamic sql query

An observation on the code you presented - when I paste it into a query window I get errors

Quote:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'IsFullTextInstalled'.

Those extra double quotes at the start and end of the code should not be there. If removed the code produces the text

SELECT CASE SERVERPROPERTY(''IsFullTextInstalled'')
    WHEN 1 THEN
        CASE DatabaseProperty (DB_NAME(DB_ID()), ''IsFulltextEnabled'')
        WHEN 1 THEN 1
        ELSE 0
        END
    ELSE 0
    END

which still produces the error. I think the code should read

exec(
'SELECT CASE SERVERPROPERTY(''IsFullTextInstalled'')
    WHEN 1 THEN
        CASE DatabaseProperty (DB_NAME(DB_ID()), ''IsFulltextEnabled'')
        WHEN 1 THEN 1
        ELSE 0
        END
    ELSE 0
    END
    ')

A technique I sometimes employ to avoid long streams of single quotes is to use CHAR(39) to represent the single quote that needs to be in the dynamic query E.g.

DECLARE @DynamicQuery Nvarchar(max) =
'SELECT CASE SERVERPROPERTY(' + CHAR(39) + 'IsFullTextInstalled' + CHAR(39) + ')
    WHEN 1 THEN
        CASE DatabaseProperty (DB_NAME(DB_ID()),' + CHAR(39) + 'IsFulltextEnabled' + CHAR(39) + ')
        WHEN 1 THEN 1
        ELSE 0
        END
    ELSE 0
    END
'
EXEC sp_executesql  @DynamicQuery

Refer this link for your answer bro

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179130[^]

another thing to Understand this

<br />
<br />
SELECT columns from mytable where col ='Lifco's'<br />
<br />
When you specify a value which has single quote, you need to double it<br />
<br />
SELECT columns from mytable where col ='Lifco''s'<br />
<br />
The following may be helpful (Run and see the result)<br />
<br />
SELECT '','''','''''','''''''',''''''''''<br />
<br />
When you use a static sql and express a value in a single quote then first and last sigle quotes specify that the value is a string. Then within those single quotes every double single quotes represent a single single quote<br />
<br />
When you use a Dynamic sql then first and last sigle quotes specify that it is a dynamic sql. Then within those single quotes every double single quotes specify that it is a string.Then within those single quotes every four single quotes represent a single single quote<br />
<br />
Run and see the result<br />
<br />
EXEC('SELECT '''','''''''','''''''''''','''''''''''''''',''''''''''''''''''''')