SQL Server 2008 SET QUOTED_IDENTIFIER OFF problem

I have a stored procedure, and inside I have a query for paging, which returns me a set of results depending on which page I am.

I have

DECLARE @Products TABLE()... then INSERT INTO @Products SELECT ROW_NUMBER()...

Everything works fine, but when I have a product with a single or double quotation marks in it's name, it doesn't sh开发者_运维技巧ow up in the results. SET QUOTED_IDENTIFIER is OFF. When I remove the quotation marks it works, but when I add them again it doesn't show up.

What's my solution here?

Is your proc calling an indexed view by any chance?

Indexed Views depend on SET QUOTED_IDENTIFIER being set to ON. The proc will backfire in the case they are used explicitly using WITH (NOEXPAND), otherwise it can also grind down to a halt since it'll treat it as a normal view. See the following article on MSDN.

Stored procedures and triggers should be written to work with the six SET options that are required to support the indexes on views and computed columns. The query optimizer does not use an index on a view or computed column in SELECT statements that are executed by a stored procedure or trigger when the SET options are incorrectly set. An INSERT, UPDATE, or DELETE statement in the stored procedure or trigger that modifies data values stored in the indexed view or computed column generates an error.