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

Question

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)

Check for HAVING cluase... normally having clause used to filter on aggregation for filtering on column you might require to use WHERE itself — Kannan Kandasamy, Jun 06 '17 at 13:52
And your problem is? This should work fine on the syntax level, but as always, you can't compare `NULL` values using `=`; the function itself would have to accommodate for it using `IS NULL`, `ISNULL` or [some other technique](https://stackoverflow.com/questions/1075142/how-to-compare-values-which-may-both-be-null-is-t-sql). — Jeroen Mostert, Jun 06 '17 at 13:54
@AlexK.: schema names are not required for table-valued functions. — Jeroen Mostert, Jun 06 '17 at 14:01

score 3 · Answer 1 · answered Jun 06 '17 at 13:56

3

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)

answered Jun 06 '17 at 13:56

JohnHC

10,935
1
24
40

**Thank you** JohnHC.. It is works fine... Cheers..! – Prince Antony Jun 06 '17 at 14:25

score 0 · Answer 2 · answered Jun 06 '17 at 13:53

0

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

answered Jun 06 '17 at 13:53

Jayvee

10,670
3
29
40

I think it is works, if we follow the logic NULLS are stored as an Empty string. – Prince Antony Jun 06 '17 at 14:14

score 0 · Answer 3 · answered Jun 06 '17 at 14:39

'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))

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

3 Answers3