Datetime returning null in a stored procedure

Question

I am executing a stored procedure inside another stored procedure.

I pass the parameters and when the value is not null, it works perfectly fine, but when the value generates as 0 rows, it gives me error.

Stored procedure:

    declare @LDate datetime
    declare @DateEntered datetime

    insert into Table1 values(5,5)

    EXEC @LDate = GetLDate @ID, @GID   // other stored procedure inside the main stored procedure
    // GetLDate is ("select Date1 from TableGetDate where @ID =4,@GID=5")

    if(CAST(@LDate as datetime) < CAST(@DateEntered as datetime))
        Select '-1'
    else
        SELECT '-2'

When in the C# code I call ExecuteScalar, it returns null if @LDate returns no date. If @LDate returns a date then everything works perfectly fine.

How to solve this?

Add a check to see if @LDate is null and handle it as you see fit. — MartianCodeHound, Jun 09 '15 at 20:42
Please clean up the code in your question (dateime, @declare, two lines commented out in the middle) and provide the code for GetLDate and perhaps we can figure this out. — MartianCodeHound, Jun 09 '15 at 20:51
possible duplicate of [How to get nullable DateTime out of the database](http://stackoverflow.com/questions/9503698/how-to-get-nullable-datetime-out-of-the-database) — Dour High Arch, Jun 09 '15 at 21:45

score 0 · Answer 1 · answered Jun 09 '15 at 20:57

0

You could safely convert the return object to a datetime if the object value is null.

DateTime dateTime = new DateTime();
try
{
 dateTime = Convert.ToDateTime(cmd.ExecuteScalar());
}
catch
{
 dateTime = new DateTime(1900, 1, 1);
}

answered Jun 09 '15 at 20:57

Mike

550
2
16

I am getting null value in SQL, so it does not moves further, i guess – hello temp11 Jun 09 '15 at 21:03

score 0 · Answer 2 · answered Jun 09 '15 at 21:16

0

You don't need to cast a datetime as a datetime. That doesn't do anything.

declare @LDate datetime
declare @DateEntered datetime

EXEC @LDate = GetLDate @ID,@GID //Other store proc inside the main store proc

if (@LDate is null)
   SELECT '0' 
else if( @LDate < @DateEntered )
    SELECT '-1'
else
    SELECT '-2'

answered Jun 09 '15 at 21:16

MartianCodeHound

355
1
8

I am sorry, it even dfoes not work when the value is not null. I updated my sql , could you have a look. – hello temp11 Jun 09 '15 at 21:19
I have just added 1 more insert line. In the sql it works fine but when in c# i do : ExecuteScalar(), it returns null. – hello temp11 Jun 09 '15 at 21:20
GetDate proc is "select Date1 from TableGetDate where @ID =4,@GID=5" – hello temp11 Jun 09 '15 at 21:23

Datetime returning null in a stored procedure

2 Answers2