Stored Procedure Default Value Not Being Set

Question

Please tell me what I am missing in this example.

I have a stored procedure where I provide a default value of an empty string for a parameter. When I check the value, it comes back as NULL. I'm not sure if the default value is not being applied, or my ISNULL check is wrong.

Here is a simple example to illustrate:

CREATE PROCEDURE TestDefaultValueParam
@vcTestName varchar(100)= ''
AS
BEGIN
    IF ISNULL(@vcTestName, '') = ''
    BEGIN
        PRINT '@vcTestName IS NULL'
    END
    ELSE
    BEGIN
        PRINT '@vcTestName NOT NULL'
    END
END

I have tried the following 3 commands:

EXEC    [dbo].[TestDefaultValueParam]   @vcTestName = NULL
EXEC    [dbo].[TestDefaultValueParam]   @vcTestName = ''
EXEC    [dbo].[TestDefaultValueParam]

The result was @vcTestName IS NULL for all three even though there is a default empty string provided in the stored procedure.

Well of course the results are all three the same. Look at your code, it is exactly what you told it. If your variable is not passed in, make it an empty string. Then if your variable is null make it an empty string. In other words, all 3 times you executed your proc the value of vcTestName is '' so the same result will happen. — Sean Lange
– Sean Lange, Commented Jan 23, 2015 at 20:18

Kritner · Accepted Answer · 2015-01-23 20:19:54Z

2

Your if statement in effect says if it's empty string OR null, then treat is as empty string (print is null) - in your exec statements, you're in all cases passing in a null or an empty string, therefore hitting your isnull logic.

declare @string varchar(20)
set @string = ''

select isnull(@string, ''), -- '' would be returned
       @string -- null would be returned.

what this means is, if @string is null, treat it as an empty string. Then you're checking for empty string and printing "string is null"

There's no code issue here, I just think you're having a brain fart maybe?

answered Jan 23, 2015 at 20:19

Kritner

13.8k11 gold badges53 silver badges76 bronze badges

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

2 Comments

L_7337 Over a year ago

OK. I was thinking it would set the parameter to an empty string and therefore skip the ISNULL logic. Thx SO is making me wait before accepting.

Kritner Over a year ago

Pretty easy mistake to make if you don't know better. Basically isNull(arg1, arg2) states, if arg1 is null, treat it as the value in arg2. In your case isNull(@vcTestName, '') says in instances where @vcTestName is null, treat it as ''. then you're comparing '' to ''.

Gordon Linoff · Accepted Answer · 2015-01-23 20:19:11Z

2

Why not just use IS NULL if that is what you want?

CREATE PROCEDURE TestDefaultValueParam
@vcTestName varchar(100)= ''
AS
BEGIN
    IF @vcTestName IS NULL
    BEGIN
        PRINT '@vcTestName IS NULL'
    END
    ELSE
    BEGIN
        PRINT '@vcTestName NOT NULL'
    END
END;

Your logic is equivalent to:

IF @vcTestName IS NULL OR vcTestName = ''

It is testing for both values.

answered Jan 23, 2015 at 20:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

knkarthick24 · Accepted Answer · 2015-01-23 20:19:28Z

2

Change condition in your code as like below:

CREATE PROCEDURE TestDefaultValueParam
@vcTestName varchar(100)= ''
AS
BEGIN
    IF @vcTestName IS NULL
        PRINT '@vcTestName IS NULL'
    ELSEIF @vcTestName = ''
        PRINT '@vcTestName IS SPACE'
    ELSE
        PRINT '@vcTestName NOT NULL'

END

answered Jan 23, 2015 at 20:19

knkarthick24

3,24619 silver badges21 bronze badges

Collectives™ on Stack Overflow

Stored Procedure Default Value Not Being Set

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related