The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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)
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;

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;

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;

DataTable dt = new DataTable();
int rows = 0;
    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 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;

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),
 from dba.pf_dailyvaluation where valuation_dt = @valuation_dt_dif


and used the same set of values to test against the c# member but got null valuation_dt_dif

where did I go wrong?

asked 20 May '14, 01:34

gg99's gravatar image

accept rate: 0%

edited 20 May '14, 18:09

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;
(20 May '14, 13:06) Chris Keating
Comment Text Removed

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

(20 May '14, 18:06) gg99

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.

(21 May '14, 09:40) Chris Keating

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.

permanent link

answered 20 May '14, 02:38

Martin's gravatar image

accept rate: 14%

edited 20 May '14, 02:39

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.

permanent link

answered 20 May '14, 05:26

AlexeyK77's gravatar image

accept rate: 8%

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...

(20 May '14, 05:42) Volker Barth

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.

(20 May '14, 06:30) AlexeyK77
Comment Text Removed
Comment Text Removed

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

(21 May '14, 00:45) gg99
Replies hidden

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.

(21 May '14, 02:48) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 20 May '14, 01:34

question was seen: 1,539 times

last updated: 21 May '14, 09:40