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''
Best Answer
You can temporarily change QUOTED_IDENTIFIER.
Rextester here
Using within the cursor
This is the result:
Rextester here