Problem with SQL Server 2008 SET QUOTED_IDENTIFIER OFF

Question

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

I have

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

Everything works fine, but when I have a product with one or two quotes in it, it does not appear in the results. SET QUOTED_IDENTIFIER is turned off. When I remove quotes, it works, but when I add them again, it does not appear.

What is my solution here?

Answer 1

Is your proc invoking an indexed view any chance?

Indexed views depend on setting SET QUOTED_IDENTIFIER to ON. Against it will have unpleasant consequences if they are used explicitly using WITH (NOEXPAND), otherwise it can also stop until it is considered as a normal view. See the article on MSDN .

Stored procedures and triggers must be written to work with six SET parameters needed to support indexes on views and calculated columns. The query optimizer does not use the index in the view or compute the column in SELECT statements that are executed by the stored procedure or when the SET parameters are incorrectly set. An INSERT, UPDATE, or DELETE in a stored procedure or trigger that changes data values ​​stored in an indexed form or a calculated column generates an error.