CODEWITHSUNDEEP
sqlsql-servert-sqlisnull
The Light
The Light

Reputation: 27011

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

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,

Upvotes: 2

Views: 630

Answers (3)

Curtis
Curtis

Reputation: 103348

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.

Upvotes: 3

user533832
user533832

Reputation:

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 |

Upvotes: 2

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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 :(.

Upvotes: 2

Related Questions