sql - Can't call stored procedure without specifying supposedly optional parameter -

i'm trying call stored procedure several required , several optional parameters. before came along, beginning of procedure scripted out this:

use [mydb] go set ansi_nulls on go set quoted_identifier on go alter procedure [dbo].[sp_getstudents] ( @sortorder varchar(50), @sortcolumn varchar(150), @sortletter varchar(10), @status varchar(250), @pageindex int, @pagesize int, @user_id int, @reference_no varchar(50) = null, @first_name varchar(50) = null, @middle_name varchar(50) = null, @last_name varchar(50) = null ) begin -- other stuff here 

everything seems work fine. added parameter end:

use [mydb] go set ansi_nulls on go set quoted_identifier on go alter procedure [dbo].[sp_getstudents] ( @sortorder varchar(50), @sortcolumn varchar(150), @sortletter varchar(10), @status varchar(250), @pageindex int, @pagesize int, @user_id int, @reference_no varchar(50) = null, @first_name varchar(50) = null, @middle_name varchar(50) = null, @last_name varchar(50) = null, @contextid int = null ) begin -- other stuff here 

and it's broken. when use ado.net call stored proc setting command object parameters, throws exception because i'm not setting @contextid parameter.

any idea why? thought if set default value of null, effectually totally optional.

update: here's how sqlcommand gets set up, best can represent here:

sqlcommand cmd = new sqlcommand("sp_getstudents"); cmd.commandtype = commandtype.storedprocedure; sqlparameter param = new sqlparameter(); param.parametername = "@sortorder"; param.value = sortorder; param.direction = parameterdirection.input; sqlparameter param2 = new sqlparameter(); param2.parametername = "@sortcolumn"; param2.value = sortcolumn; param2.direction = parameterdirection.input; cmd.parameters.add(param); cmd.parameters.add(param2); cmd.parameters.add(new sqlparameter("@sortletter", sortletter)); cmd.parameters.add(new sqlparameter("@status", status)); cmd.parameters.add(new sqlparameter("@pageindex", pageindex)); cmd.parameters.add(new sqlparameter("@pagesize", pagesize)); // here, code splits search expression multiple parameters, looping on switch statement this: foreach (string token in tokens) { switch(token) { case "reference_no": cmd.parameters.add(new sqlparameter("@reference_no", (object)value ?? dbnull.value)); break; case "first_name": cmd.parameters.add(new sqlparameter("@first_name", (object)value ?? dbnull.value)); break; case "last_name": cmd.parameters.add(new sqlparameter("@last_name", (object)value ?? dbnull.value)); break; case "middle_name": cmd.parameters.add(new sqlparameter("@middle_name", (object)value ?? dbnull.value)); break; case "generation": cmd.parameters.add(new sqlparameter("@generation", (object)value ?? dbnull.value)); break; case "contextid": cmd.parameters.add(new sqlparameter("@contextid", (object)value ?? dbnull.value)); break; } } cmd.parameters.addwithvalue("@user_id", userid); // fires off cmd through dal 

here's exception (of type system.data.sqlclient.sqlexception):

the parameterized query '(@reference_no varchar(50), @first_name varchar(5' expects parameter '@contextid', not supplied.

i find message kind of odd because formatting seems jacked. anyway, under hood command gets executed via sqldataadapter used fill dataset. exception thrown during fill method execution.

on http://msdn.microsoft.com/en-us/library/ms187926.aspx link following line mentioned..

the value of each declared parameter must supplied user when procedure called unless default value parameter defined or value set equal parameter. if procedure contains table-valued parameters, , parameter missing in call, empty table passed in.

so based on can if put "default" key work problem resolved.

also on same page information regarding default mention details following.

default default value parameter. if default value defined parameter, procedure can executed without specifying value parameter. default value must constant or can null. constant value can in form of wildcard, making possible use keyword when passing parameter procedure. see example c below.

default values recorded in sys.parameters.default column clr procedures. column null transact-sql procedure parameters.

thanks, jigar