I wish to refer to this as method that transmits one value that may be NULL or any int value.

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

I only desire to make use of this single query instead of things i m doing at this time in below given code.

I looked with this how could i actually do this i quickly got this Link.

Based on this I must set ANSI_NULLS OFF

I'm not in a position to set this inside this process before performing my sql query after which totally reset it again after carrying this out.

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

What would be the better practice to complete cope with ANSI_NULLS or Using If Else

SET ANSI_NULLS is ony defined at saved proc create some time and can't be set at run time.

From CREATE PROC

Using SET Options

The Database Engine saves the configurations of both SET Cited_IDENTIFIER and hang ANSI_NULLS whenever a Transact-SQL saved procedure is produced or modified. These original configurations are utilized when the saved procedure is performed. Therefore, any client session configurations for SET Cited_IDENTIFIER and hang ANSI_NULLS are overlooked once the saved procedure is running. Other SET options, for example SET ARITHABORT, SET ANSI_Alerts, or SET ANSI_PADDINGS aren't saved whenever a saved procedure is produced or modified. When the logic from the saved procedure is dependent on the particular setting, incorporate a SET statement at the beginning of the procedure to be sure the appropriate setting. Whenever a SET statement is performed from the saved procedure, the setting remains essentially only until the saved procedure has finished running. The setting will be restored towards the value the saved procedure had if this was known as. This allows individual clients to create the choices they need without having affected the logic from the saved procedure.

Exactly the same is applicable to [cde]

Within this situation, use IF ELSE because SET ANSI_NULLS is going to be ON later on.

Or Peter Lang's suggestion.

To tell the truth, expecting SET QUOTED_IDENTIFIER to operate when @SubDomainId is NULL isn't correct use of NULL...

Can't you utilize just one query?

SubDomainId = @SubDomainId

FYI, I am confident ...

SELECT DomainName, DomainCode
FROM Tags.tblDomain
WHERE ( @SubDomainId IS NULL AND SubDomainId IS NULL )
   OR ( SubDomainId = @SubDomainId )

Is applicable towards the procedure whenever you create/edit it, it's just like a setting from the procedure.

So either the process has it Off or on. Your example would be a query not really a procedure so I am a little confused.

But when you've SQL 2005/2008 for instance should you "edit" procedure it reveals your procedure inside a new tab you will see the ANSI_NULLS OFF close to the top.

You are able to edit it there and hang it Off or on increase it to alter ...