Optional Parameter value ignored

Question

Ran into this issue, and I don't know why it won't work.

I have a stored procedure that has an optional 2nd parameter.

PROCEDURE [dbo].[gsp_MyProC] @Account_Number VARCHAR(10), @Referral_Type VARCHAR(5) ='1'

Now, @Referral_Type gets used in a SQL statement, and currently never gets passed into the procedure (legacy code, don't ask). However, sometimes it doesn't get set to one, its just left to null (verified by returning the parameter).

If I use:

SET @Referral_Type = 1

inside the stored procedure, It always gets set (as would be expected).

But does anyone know why the default value wouldn't be getting set to 1?

Thanks

Answer 1

There is a distinction between not providing a parameter, and providing a NULL value for a parameter...

EXEC dbo.gsp_MyProC 'AccountNumber', NULL

Will result in @Referral_Type = NULL

However...

EXEC dbo.gsp_MyProC 'AccountNumber'

Will result in @Referral_Type = '1'

It is likely that your client library is providing a NULL value instead of not providing any value at all.

As a work-around... if @Referral_Type should never be NULL, you can add SET @Referral_Type = ISNULL(@Referral_Type, '1') at the beginning of your stored procedure.