I like them primarily when I may have a value that can be NULL just so that all the parameters do not need to be expressed. But to make usefull to most folks you need to put defaulted values toward the end otherwise they in some cases end up doing more work than needed.

Ex.

CREATE PROC ip_test

@val1 as varchar(4) = NULL,

@val2 as varchar(4) = NULL

nw I want to set @val2 to TEST and leave @val1 out

ip_test @val2 = 'TEST'

ip_test NULL, 'TEST'

So there you see the value list was shorter to code than having to specify @val2. This sometimes can be a drawback if you use highly desciptive variable names one small data values. It is just a nicity that you have the option.

However in the example if I wanted to set @val1 to TEST then I would do

ip_test 'TEST'

which is short than the other two. My opinion is supply all values up to the last item you need and anything that can be defaulted put at the end of you list of inputs. Thus making it of the most possible use.