1

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

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

| improve this answer | |
  • How would I implement this in my dynamic SQL case? – BellHopByDayAmetuerCoderByNigh Apr 28 '17 at 23:08
  • I've edited the answer. – McNets Apr 28 '17 at 23:18
  • I get an error of 'CONCAT' is not a recognized built-in function name. - is this SQL Server 2008 R2 friendly? – BellHopByDayAmetuerCoderByNigh Apr 28 '17 at 23:20
  • No, I'm sorry. I'll change it by + – McNets Apr 28 '17 at 23:22
  • This works! SET @SQL = 'SET QUOTED_IDENTIFIER OFF Select * from TestHyphens where storename = "' + storename + '" SET QUOTED_IDENTIFIER ON'; Thank you! – BellHopByDayAmetuerCoderByNigh Apr 28 '17 at 23:23
0

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
| improve this answer | |
  • Removing one CHAR(39) and a set of the single quotes in my replace statement, changes the output from ''Bob''''s Burger''''s'' TO 'Bob''s Burger''s' - so instead of having triple quotes before the s I only have double now. Still syntax issue. – BellHopByDayAmetuerCoderByNigh Apr 28 '17 at 23:11
0

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''
| improve this answer | |
  • Yes the 1st one is my desired output. I added in the replace statement as the single quote needs to be double quoted in the Where clause or an error of Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Bob'. is presented. – BellHopByDayAmetuerCoderByNigh Apr 28 '17 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.