SQL Server 2008 SET QUOTED_IDENTIFIER OFF 问题

Question

我有一个存储过程，在里面我有一个分页查询，它根据我所在的页面返回一组结果。

我有

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

一切正常，但是当我的产品名称中带有单引号或双引号时，它不会显示在结果中。 SET QUOTED_IDENTIFIER 已关闭。当我删除引号时它可以工作，但是当我再次添加它们时它不会出现。

我的解决方案是什么？

Answer 1

您的 proc 是否正在调用 indexed view在任何情况下？

索引 View 取决于设置为 ON 的 SET QUOTED_IDENTIFIER。在使用 WITH (NOEXPAND) 显式使用它们的情况下，proc 会适得其反，否则它也可能会停止，因为它会将其视为普通 View 。见以下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.