what is wrong is this storeproc inout parm

[g_g_99]

problem: I was unable to the the new value out of a SP inout parm executed from .net 4.5

// simplified to highlight the inout parm being null always, no problem with out parm though

private DataTable daily_holdg_detl_variance(DateTime valuation_dt, int acct_grp_id, ref DateTime? valuation_dt_dif, out string Msg)

{ SAConnection myConnection = ModuleDb.myConnection; // a c# module to setup the connection...

SACommand myCmd = new SACommand("dba.pf_daily_holdg_detl_variance", myConnection);
myCmd.CommandType = System.Data.CommandType.StoredProcedure;
if (myConnection.State.CompareTo(ConnectionState.Broken) == 0 |
    myConnection.State.CompareTo(ConnectionState.Closed) == 0)
 myConnection.Open();
SAParameter parm0 = new SAParameter("@valuation_dt", SADbType.Date);
parm0.Precision = 0;
parm0.Scale = 6;
parm0.Size = 4;
parm0.Direction = ParameterDirection.Input;
parm0.IsNullable = false;
parm0.SourceColumnNullMapping = false;
parm0.Value = valuation_dt;
myCmd.Parameters.Add(parm0);

SAParameter parm1 = new SAParameter("@acct_grp_id", SADbType.Integer);
parm1.Precision = 0;
parm1.Scale = 0;
parm1.Size = 4;
parm1.Direction = ParameterDirection.Input;
parm1.IsNullable = false;
parm1.SourceColumnNullMapping = false;
parm1.Value = acct_grp_id;
myCmd.Parameters.Add(parm1);

SAParameter parm_Valuatn_Dt_dif = new SAParameter("@valuation_dt_dif", SADbType.Date);
parm_Valuatn_Dt_dif.Precision = 0;
parm_Valuatn_Dt_dif.Scale = 6;
parm_Valuatn_Dt_dif.Size = 4;
parm_Valuatn_Dt_dif.Direction = ParameterDirection.InputOutput;
parm_Valuatn_Dt_dif.IsNullable = true;
parm_Valuatn_Dt_dif.SourceColumnNullMapping = false;
parm_Valuatn_Dt_dif.Value = valuation_dt_dif;
myCmd.Parameters.Add(parm_Valuatn_Dt_dif);

DataTable dt = new DataTable();
int rows = 0;
try
{
    SADataAdapter saDataAdapter = new SADataAdapter(myCmd);
    rows = saDataAdapter.Fill(dt);
    valuation_dt_dif = Util.obj2DateTime(parm_Valuatn_Dt_dif.Value);
}catch (Exception er)
{ Msg = "error in setup DataAdapter or filling DataTable: " + er.Message; }
}



the stored proc for sa 11.0.1.2960 is like:
PROCEDURE "dba"."pf_daily_holdg_detl_variance"(
                         in    @valuation_dt     date,
                         in    @acct_grp_id      integer,
                         inout @valuation_dt_dif date default null )
    if (@valuation_dt_dif is null) then
    select max(valuation_dt) 
          into @valuation_dt_dif
      from dba.dba.pf_daily_holdg_detl  //pf_dailyvaluation
      where valuation_dt between dateadd(day,-40,@valuation_dt) 
            and dateadd(day,-1,@valuation_dt)
     and acct_grp_id=@acct_grp_id;
    end if;
end


I verified I was to able to get non null valuation_Dt_dif in isq with 
begin
    declare @test decimal
declare @acct_Grp_id integer

declare @valuation_dt datetime

declare @valuation_dt_dif datetime

set @valuation_dt = '2013-04-26 00:00:00'
set @acct_grp_id = 1
set @valuation_dt_dif = null
    select max(valuation_dt) as mxValnDt into @valuation_dt_dif
     from dba.dba.pf_daily_holdg_detl   //pf_dailyvaluation
     where valuation_dt between dateadd(day,-40,@valuation_dt) and dateadd(day,-1,@valuation_dt)
      and acct_grp_id=@acct_grp_id
      //and exists(select 1 from dba.pf_daily_holdg_detl where date(valuation_dt)=date(mxValnDt))

select @valuation_dt, @valuation_dt_dif, @@ROWCOUNT,dateadd(day,-40,@valuation_dt),dateadd(day,-1,@valuation_dt),
 count(*)
 from dba.pf_dailyvaluation where valuation_dt = @valuation_dt_dif


end
and used the same set of values to test against the c# member but got null valuation_dt_dif
where did I go wrong?

[chris_keating]

A DateTime value cannot be null in .NET. You are likely getting "1/1/0001 12:00:00 AM" returned since that is the declared value of DateTime if not set with a value. If you know that 1/1/0001 is not a valid Date value for your implementation, you will need to check for that case and explicitly set the parameter value to null as in:

if (v2.CompareTo(new DateTime()) == 0)
// assumes that 1/1/0001 12:00:00 AM 
// represents a null datetime value
{
   parm_Valuatn_Dt_dif.Value = null;
} else {
   parm_Valuatn_Dt_dif.Value = v2;
}

[g_g_99]

I was using DateTime? valuation_dt_dif; so I was able to store null value there. if (parm_Valuatn_Dt_dif.Value !=DBNull.Value) valuation_Dt_dif=Util.object2DateTime(parm_Valuatn_Dt_dif.Value)

Also I stepped thru the c# side and I did see "no" value not any date value in parm_Valuatn_Dt_dif.Value btw. the actual stored proc does return result set but the sql are quite quite long and complex, has a series of if else, and most have unions

[chris_keating]

I am not following how you set DateTime to a value of NULL, be it a NULL or DBNull.Value. The following is reported when I attempt to do so: 1) Cannot convert null to 'System.DateTime' because it is a non-nullable value type 2) Cannot implicitly convert type 'System.DBNull' to 'System.DateTime'

Regardless, I get the expected behaviour if I set the parameter value to null or DBNull.Value.

I recommend that you debug the procedure to see what value it is receiving from the client. I think that you will find that it is not being received as a NULL value as you expect.

[Former Member]

Historical remark. 7-8 years ago I had problems with stored procedures that simultaneously return data via resultset and out params. Using ODBC/ADO invocation of that kind of procedures always return resultset, but out params was empty. So, I think that ODBC/ADO SA drivers has bug, that still don't resolved.

[VolkerBarth]

Hm, in my understanding the procedure in question does not return a result set. Therefore I would think the problem with accessing both a result set and inout/out parameters and return values from a procedure (that is still an existing problem/feature AFAIK) should not apply here...

[Former Member]

Volker, you are absolutely right, but from centures to centures DB vendors and especially sybase still produce buggy client side drivers. Anybody know, that native drivers (ctlib/cli/etc) has much less bugs and problems.

gg99 wrote, that in ISQL there are no problems, so probably problems in client side drivers or .NET DB-side library.

[g_g_99]

I was hoping it is something I did wrong instead of the .net or odbc driver. hated to delve into the sql and add that column. that require a lot of test scenario.

is it bad idea/practice to return that date as another result set and let the c# member to pick that up? or just code the default valuatn_dt_dif as separate function to let the c# call and the stored proc to call

I suspect I will run into the same problem in sa 16 in the future. unless someone find it otherwise

[MCMartin]

As Volker has correctly seen, your stored procedure is not returning a result set, so using the SADataAdapter might not help, try the ExecuteNonQuery instead.

[MCMartin]

Just change the SQL of your command to something like

select * from dba.pf_daily_holdg_detl_variance(param0,param1...)


then you will get your result set without the hassle of the Parameter objects.