CODEWITHSUNDEEP
sqlsql-servert-sql
Prince Antony
Prince Antony

Reputation: 41

How to call a user defined function with NULL values for one of its input parameter?

My UDF's body has the following HAVING class. 

HAVING   
         Company.Description            = @Company       AND
         SystemCustomerType.Description = @CustomerType

I tried to call this by this following syntax.

SELECT * FROM FunctionName('ABC_Company',NULL)

And also tried to set default value as NULL for @CustomerType parameter. And called the function by

SELECT * FROM FunctionName('ABC_Company',default)

Upvotes: 0

Views: 1102

Answers (3)

Peter
Peter

Reputation: 850

'something' = NULL always returns false. To check if something is NULL use 'is'.

HAVING   
     Company.Description            = @Company       AND
     (SystemCustomerType.Description = @CustomerType 
     or (SystemCustomerType.Description is null and @CustomerType  is null))

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

Try this:

where (Company.Description = @Company or @Company is null)
and (SystemCustomerType.Description = @CustomerType or @CustomerType is null)

Then use:

select * from FunctionName('ABC_Company',NULL)

Upvotes: 3

Jayvee
Jayvee

Reputation: 10875

you can use NULL as parameter and then:

HAVING   
         Company.Description            = @Company       AND
         SystemCustomerType.Description = isnull(@CustomerType,'')

provided you don't need to differenciate between NULL and empty string

Upvotes: 0

Related Questions