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
– Kannan Kandasamy, Commented Jun 6, 2017 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. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 6, 2017 at 13:54
@AlexK.: schema names are not required for table-valued functions. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 6, 2017 at 14:01

JohnHC · Accepted Answer · 2017-06-06 13:56:42Z

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 6, 2017 at 13:56

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prince Antony Over a year ago

Thank you JohnHC.. It is works fine... Cheers..!

Jayvee · Accepted Answer · 2017-06-06 13:53:55Z

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 6, 2017 at 13:53

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

1 Comment

Prince Antony Over a year ago

I think it is works, if we follow the logic NULLS are stored as an Empty string.

Peter · Accepted Answer · 2017-06-06 14:39:52Z

0

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

answered Jun 6, 2017 at 14:39

Peter

8506 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related