Is an empty string considered an empty parameter in a stored procedure?

Question

I have this stored procedure

ALTER PROC myProcedure 
    @day VARCHAR (6) = NULL
as

Now if I call it like this

EXEC myProcedure @day= ''

What will be the result of @day? Will it be empty string or NULL?

My theory is, if the parameter was omitted completely, it would be NULL, but now it will be empty string. Can anyone confirm?

ughai · Accepted Answer · 2015-03-25 08:52:04Z

1

If you pass an empty string, the param value is empty string. You can easily check by selecting the variable as shown below

IF OBJECT_ID('myProcedure') IS NOT NULL
DROP PROC myProcedure 
GO
CREATE PROC myProcedure 
    @day VARCHAR (6) = NULL
as
BEGIN
    SELECT @day as param
END
GO
--Returns empty string in output
EXEC myProcedure @day= ''

--Returns NULL as output
EXEC myProcedure

Note: the reason NULL is returned is because the parameter is defaulted to NULL in the procedure parameter definition. If the default was omitted, the EXEC myProcedure will return an error.

answered Mar 25, 2015 at 8:52

ughai

9,9004 gold badges33 silver badges48 bronze badges

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

1 Comment

Zikato Over a year ago

Thank you. That's what I thought, but I couldn't check it myself for various reasons (permissions, etc.)

Collectives™ on Stack Overflow

Is an empty string considered an empty parameter in a stored procedure?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related