sql - stored procedure with isnull(expression, replacement) return nothing -

i have stored procedure accepts parameter , return tuple matching values. if no parameter passed, return every tuple in table

create procedure getscore (     @cliniccode varchar = null, ) begin     select * mytable     cliniccode = isnull(@cliniccode, cliniccode) end 

so executte it

exec getscore exec getscore 'psh' 

both of them return no tuple. did try select * mytable, , returns tuples. not sure why statement ... isnull(expression, replacement) messed up

you need change declaration of

@cliniccode varchar = null, 

to actual size require.

so like

@cliniccode varchar(50) = null, 

the reason that

@cliniccode varchar 

is same

@cliniccode varchar(1) 

which casts field isnull(@cliniccode, cliniccode) first letter of cliniccode

have @ example

sql fiddle demo