Design question

According to transact-sql docs, comparing two nulls when SET ANSI_NULLS is ON, gives back an UNKNOWN, which should evaluate to FALSE in an expression... i guess.

The question is, since i need SET ANSI_NULLS ON in all my stored procedures (i need NULL = <Expr> evaluates to false), where should i instead set it? I mean, is there some database configuration flag somewhere?

Thanks a lot in advance. -julioIt really isn't clear what you are asking...if you are trying to determine if an expression is null when you have ansi_nulls ON then you can use 'IS'. If you could give some code in what you are trying it may help...|||SELECT * FROM [Table] WHERE [Field] LIKE @.Field

I'd like to be sure the evaluation fails (selects an empty table) when [Field] or @.Field are NULL...

Thanks. -julio|||"Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown."

That was in BOL. I hope it helps in understanding why I am stating what I am. What you have should evaluate to false and thus not return any records. The like operator will try to convert [Field] to a varchar so depending on the type it may fail.

David|||[Field] and @.Field are both char (nvarchar actually).

BTW, i still can't get what the answer to my original question is...

Do i need the SET ANSI_NULLS ON?
And, is there any database level configuration flag to set it globally?

Thanks for your kind help. -julio|||Yes you want to set it on. And no you can't set it at the database level. It is set when the object is created. I would also recommend using varchar instead of nvarchar unless need to store unicode data.|||Ok, got it! Thanks a lot for your help.

-julio