Procedure or function 'usp_myprocedureName' expects parameter '@param', which was not supplied

I came with an SQL exception "Procedure or function 'usp_myprocedureName' expects parameter '@param', which was not supplied".

Normally this exception is caused when the parameter is not exists in your SQLcommand object or spelling mistake in parameter name. I check my .net code and confirmed parameter is exists!!!, then i go to SQL server and manually executed my SP with same parameter I copied from my .net code like below

execute usp_myStoreProcedure @Param = 'xxxxx'

it got executed !!!, to re-produce the issue i put NULL and empty string for @Param like below,

execute usp_myStoreProcedure @Param = ''
execute usp_myStoreProcedure @Param = NULL

still it executed with no exception.

i am confused, where was the problem then ??? 😬

Finally I found the issue 😍

The problem is here, when I set @param as NULLs from my .net SQL Command object, this NULL value is not sending to SQL and hence the issue. To check what is happening in SQL, I profile SQL, after execute my command, I found SQL server executes below command if @param becomes NULL.

exec [usp_myStoreProcedure @param=default

I resolved my issue by validate NULL, if @param value is null I skip execute stored procedure. you could resolve by your own logic.

The intention behind this post is to let you know above exception can throw even your SQL parameter has a NULL value.