Running stored procedure that has default values

[Zamdrist]

My stored procedure has several input parameters, all of which have
default values. In Query Analyzer you can of course run the procedure,
providing no parameters as they all have default values.

How can you run the same procedure in ADO.NET and not provide
parameters? On the surface it seems you cannot as intellisense picks
up the fact there are parameters and requires them, even if they have
default values.

Note, I'm not talking about the DefaultValue property of the Parameter
object, I'm talking about the default value of a T-SQL stored
procedure parameter.

Thanks

 

[sloan]

Since I haven't seen another post, I'll make an attempt or provide a
possible hint.

I'm not sure exactly. However, when my parameters have null or empty
values, I end up with something like this in my DAL code.

if (null != parameterDS)

{

db.AddInParameter(dbc, "@xml", DbType.Xml, parameterDS.GetXml());

}

else

{

db.AddInParameter(dbc, "@xml", DbType.Xml, null);

}


That is EnterpriseLibary.Data syntax, fyi.

 

[William Vaughn \(MVP\)]

There are a couple of options:
1) Build the Command object (that points to the SP) without populating the
Parameters collection for those parameters for which default values have
been assigned. Since SqlClient uses named parameters, you only need to
populate the Parameters collection with the named parameters that need to be
assigned values.
2) For any Parameter which should use the server-side declared default
value, set the Parameter property to null in C# or Nothing in VB.NET.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________