Dynamic SQL With Double Quoting I am attempting to run a dynamic SQL statement and it works for any storename that does not have an apostrophe in it.  However, when the storename has an apostrophe in it the syntax does not properly, however I am stuck using dynamic SQL in this instance.

How should I ammend this syntax in order for storename to include an apastrophe?

Here is DDL

Declare @TestHyphens Table(StoreName varchar(500),topItemSale varchar(500))

Insert Into @TestHyphens (StoreName, topItemSale) Values
('Bob''s Burger''s','Pen'), ('Jacks Coffee Shop','Pencil')
,('Larry''s Hair Shack','Rainbow'), ('Manny''s Sandwich Shop','Sunglasses')

Declare @sql nvarchar(4000), @storename varchar(500)

Declare db_cursor CURSOR FOR
Select StoreName
FROM @TestHyphens

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @storename

While @@FETCH_STATUS = 0
BEGIN

        Set @storename = REPLACE(@storename, '''', '''''''''');

        SET @storename = CHAR(39)+CHAR(39)+@storename+CHAR(39)+CHAR(39);

        SET @SQL = N'Select * from @TestHyphens where storename IN ('+@storename+ N')'

        Print @storename
        Print @sql

    FETCH NEXT FROM db_cursor INTO @storename

END

Close db_cursor
DEALLOCATE db_cursor


And print statements produce:

''Bob''''s Burger''''s''
Select * from @TestHyphens where storename IN (''Bob''''s Burger''''s'')
''Jacks Coffee Shop''
Select * from @TestHyphens where storename IN (''Jacks Coffee Shop'')
''Larry''''s Hair Shack''
Select * from @TestHyphens where storename IN (''Larry''''s Hair Shack'')
''Manny''''s Sandwich Shop''
Select * from @TestHyphens where storename IN (''Manny''''s Sandwich Shop'')


And as you can see these are incorrectly hyphenated - 

''Manny''''s Sandwich Shop''
''Larry''''s Hair Shack''
''Bob''''s Burger''''s''
You can temporarily change QUOTED_IDENTIFIER.

DECLARE @TestHyphens TABLE(StoreName varchar(500),topItemSale varchar(500))

INSERT INTO @TestHyphens (StoreName, topItemSale) VALUES
('Bob''s Burger''s','Pen'),
('Jacks Coffee Shop','Pencil'),
('Larry''s Hair Shack','Rainbow'), 
('Manny''s Sandwich Shop','Sunglasses');

SET QUOTED_IDENTIFIER OFF

SELECT StoreName, TopItemSale 
FROM   @TestHyphens 
WHERE  StoreName = "Bob's Burger's"

SET QUOTED_IDENTIFIER ON

| StoreName      | topItemSale |
|----------------|-------------|
| Bob's Burger's | Pen         |


Rextester here

Using within the cursor

DECLARE @sql nvarchar(MAX), @storename varchar(500)

DECLARE db_cursor CURSOR FOR
SELECT StoreName
FROM #TestHyphens

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @storename

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = N'SET QUOTED_IDENTIFIER OFF '
               + N'Select * from #TestHyphens where StoreName = "' + @storename + N'"'
               + N'SET QUOTED_IDENTIFIER ON';

    EXECUTE SP_EXECUTESQL @SQL;    

    FETCH NEXT FROM db_cursor INTO @storename
END

CLOSE db_cursor
DEALLOCATE db_cursor


This is the result:

| StoreName             | topItemSale |
|-----------------------|-------------|
| Bob's Burger's        | Pen         |
| Jacks Coffee Shop     | Pencil      |
| Larry's Hair Shack    | Rainbow     |
| Manny's Sandwich Shop | Sunglasses  |


Rextester here