Getting C # stored procedure value

Question

I tried different ways, but no luck. Using Microsoft Visual Studio and SQL Server 2005 here is the C # code and sql code

List<SqlParameter> _params3 = new List<SqlParameter>(); _params3.Add(new SqlParameter("@startdate", txtDateFrom.Text)); _params3.Add(new SqlParameter("@enddate", txtDateTo.Text)); _params3.Add(new SqlParameter("@days", extendedDays)); extendedDays = Convert.ToInt32(DAL.executeStoredProcedureScalar( "Time_Difference_Calc", _params3)); 

SQL code:

 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[Time_Difference_Calc] -- Add the parameters for the stored procedure here @startdate datetime, @enddate datetime, @days int output AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; set @days = (Select RawDays - NumWeeks * 2 + CASE WHEN StartWD = 1 THEN 1 ELSE 0 END - CASE WHEN EndWD = 7 THEN 1 ELSE 0 END + 1 AS Result FROM (SELECT Datepart(dw,@startdate) as StartWD, Datepart(dw,@enddate) as EndWD, DateDiff(d,@startdate,@enddate) as RawDays, DateDiff(wk,@startdate,@enddate) as NumWeeks ) A) --SET @ReturnValue = @days RETURN @days END 

I can run it in the database just fine .. way 2 dates in .. works fine for what I need.

But whenever I actually run it on the page .. always get

System.NullReferenceException: An object reference is not set to an object instance. "
Original error: return cmd.ExecuteScalar (). ToString ();

Any help is appreciated. To a large extent, the idea is that I just want to get extended days equal to what the stored procedure returns.

Answer 1

Modify the stored procedure to select a value instead of using a parameter. Then you can continue to use ExecuteScalar .

The stored procedure will look something like this:

 ALTER PROCEDURE [dbo].[Time_Difference_Calc] -- Add the parameters for the stored procedure here @startdate datetime, @enddate datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Select RawDays - NumWeeks * 2 + CASE WHEN StartWD = 1 THEN 1 ELSE 0 END - CASE WHEN EndWD = 7 THEN 1 ELSE 0 END + 1 AS Result FROM (SELECT Datepart(dw,@startdate) as StartWD, Datepart(dw,@enddate) as EndWD, DateDiff(d,@startdate,@enddate) as RawDays, DateDiff(wk,@startdate,@enddate) as NumWeeks ) A END 

One more thing to keep in mind, if you just call it from the application, then the stored procedure looks useless. You do not need SQL Server for this calculation, and it is easier to do this in code.

Answer 2

You need to set the @day Direction ParameterDirection for ParameterDirection .Output

In addition, you will need to get the value from @day SqlParamater . Value property

Answer 3

It is not possible to see the actual code that calls SQL, it is difficult to be sure, but I am sure that you should call ExecuteNonQuery for this - you actually do not select any return values, therefore, null if your code is trying to get a scalar value.

If your stored procedure had a section in it in the lines SELECT TOP 1 foo FROM bar WHERE baz = @quux; , then ExecuteScalar (or your equivalent of this) would be approriate. However, you are returning the value as an output parameter, so this is a non-query.

Note: other answers are correct about the need to set the direction of the parameters, but that is why you get a NullReferenceException.

Answer 4

 _params3.Direction = System.Data.ParameterDirection.Output; 

execute proc

 extendedDays = Convert.ToInt32(_params3.Value); 

Answer 5

 SqlParameter daysParameter = new SqlParameter("@days", extendedDays); daysParameter .Direction = ParameterDirection.Output; _params3.Parameters.Add(daysParameter ); 

gotta do the trick