sql - Default null values in stored procedures are not Null -

i have been testing faulty procedure, , realised problem null values being passed in not being defaulted procedure value...

create procedure testvarcharmaxisnull (@myvar varchar(max) = '') select 'hello' @myvar null go exec testvarcharmaxisnull null go exec testvarcharmaxisnull '' go exec testvarcharmaxisnull  go drop procedure testvarcharmaxisnull 

output

run 1 - "hello" run 2 - "" run 3 - "" 

i assumed null values defaulted value assigned in stored procedure parameter, shows if exists parameter takes value pass in. there setting in sql server can change behaviour?

the empty string '' not equivalent null value.

so say

(@myvar varchar(max) = '') 

means when don't pass in argument empty string in @myvar, in turn means when say

where @myvar null 

will evaluate true when explicitly pass in null argument, in first test case.