How to Replace IS NULL and != with a Built-in TSQL Function?

Question

Is there any simple function in TSQL that could replace the below tsql?

SELECT * FROM Users
WHERE (Username IS NULL OR Username != @username)

I could write the below but that's not bullet proof due to the hard coded text!

 SELECT * FROM Users
    WHERE ISNULL(Username, 'dummytext') != @username

Thanks,

Answer 1

In this instance I don't think its worth having a UDF to manage this condition.

(Username IS NULL OR Username != @username) is 43 characters long

dbo.IsNullorNotEqual(Username, @Username)=0 is 43 characters long

---

Sure, you could make the function name slightly shorter, but its not worth breaking convention to make a function call shorter.

Furthermore, not using a UDF in this instance will let you see exactly whats going on.

Answer 2

Daniel asked good question...

My 3 cents:

1. Using function make impossible to use index :(.
2. However != operator is supported by most databases (based on this answer, <> is ANSI compliant.
3. You can use COALESCE() instead of ISNULL() but it still function which blocking using index :(.

Answer 3

I'm not certain what you are trying to achieve, but does this help?

SQL Fiddle

Schema Setup:

create table Users(Username varchar(99));
insert into Users(Username) values('Alice');
insert into Users(Username) values('Bob');
insert into Users(Username) values(null);

Query 1:

DECLARE @username varchar(99)
SET @username = 'Alice'
SELECT * FROM Users 
EXCEPT
SELECT * FROM Users where Username = @username

Results:

| USERNAME |
------------
|   (null) |
|      Bob |