sql - 如何处理 SET ANSI_NULLS ON 或 OFF？

Question

我想调用这个发送一个值的过程，该值可以是 NULL 或任何 int 值。

SELECT DomainName, DomainCode FROM Tags.tblDomain WHERE SubDomainId =@SubDomainId

我只是想使用这个单一的查询，而不是我现在在下面给定的代码中正在做的事情。

我搜索了这个我怎么能这样做然后我得到了this Link .

根据这个，我必须将 ANSI_NULLS 设置为 OFF

我无法在执行我的 sql 查询之前在此过程中设置它，然后在执行此操作后再次重置它。

ALTER PROCEDURE [Tags].[spOnlineTest_SubDomainSelect] 
    @SubDomainId INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    IF @SubDomainId IS NULL
        SELECT DomainName, DomainCode FROM Tags.tblDomain WHERE SubDomainId IS NULL 
    ELSE
        SELECT DomainName, DomainCode FROM Tags.tblDomain WHERE SubDomainId =@SubDomainId
END

处理 ANSI_NULLS 或使用 If Else 的更好做法是什么

Answer 1

SET ANSI_NULLS 仅在存储过程创建时定义，不能在运行时设置。

From CREATE PROC

Using SET Options

The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL stored procedure is created or modified. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the stored procedure is running. Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or modified. If the logic of the stored procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure has finished running. The setting is then restored to the value the stored procedure had when it was called. This enables individual clients to set the options they want without affecting the logic of the stored procedure.

同样适用于SET QUOTED_IDENTIFIER

在这种情况下，使用 IF ELSE 因为 SET ANSI_NULLS将来会ON。

或者 Peter Lang 的建议。

老实说，期望 SubDomainId = @SubDomainId 在 @SubDomainId 为 NULL 时工作并不是 NULL 的正确用法...