2

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,

The Light
  • 26,341
  • 62
  • 176
  • 258

3 Answers3

3

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.

Curtis
  • 101,612
  • 66
  • 270
  • 352
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 :(.
Community
  • 1
  • 1
Grzegorz Gierlik
  • 11,112
  • 4
  • 47
  • 55
2

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 |