Dynamic SQL With Double Quoting

Question

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''

McNets · Accepted Answer · 2017-04-30T08:29:12.903

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

score 0 · Answer 2 · answered Apr 28 '17 at 22:46

I think you have too many hyphens going on. I was able to successfully test it using this for the cursor:

WHILE @@FETCH_STATUS = 0
BEGIN

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

        SET @storename = CHAR(39)+@storename+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

score 0 · Answer 3 · answered Apr 28 '17 at 23:07

Not sure what you are trying to accomplish but this:

While @@FETCH_STATUS = 0
BEGIN
   Print @storename
        Set @storename = REPLACE(@storename, '''', '''''''''');
   Print @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

outputs the following, is not the first one what you are after?

Manny's Sandwich Shop
Manny''''s Sandwich Shop
''Manny''''s Sandwich Shop''

Dynamic SQL With Double Quoting

3 Answers3

Linked