CODEWITHSUNDEEP
c#asp.net-mvc-5
Niana
Niana

Reputation: 1047

Decimal places not being returned from SQL Stored procedure

Situation

As the question says, I have a program and upon entering a value, this value is sent to my store procedure and 80 and 20% is calculated and returned via out parameter.

Problem

The problem is, despite how I think that my code should be working, the decimal place isn't returned when calculating my 80 and 20 percent. I have looked over the code and running the code in SQL manager works correctly, I just cant get the correct values into my C# Application.

C# Code

        decimal fl_covByA;
        decimal fl_covByB;
        using (Bookstore_DBEntities x = new Bookstore_DBEntities())
        {


            ObjectParameter covByIndiv = new ObjectParameter("covByIndiv", typeof(decimal));
            ObjectParameter covByIns = new ObjectParameter("covByIns", typeof(decimal)); 
            x.cal_InsCoverage(cla_bookDetails.cost,covByIndiv, covByIns); //Breakpoint shows values without decimals
            fl_covByB = (decimal)covByIndiv.Value;
            fl_covByA = (decimal)covByIns.Value;
        }

Store Procedure

CREATE PROCEDURE [dbo].[cal_InsCoverage]
@cost numeric(18,2),
@covByIndiv numeric(18,2) OUT,
@covByIns numeric(18,2) OUT
AS
SELECT @covByIndiv=(@cost*20)/100,@covByIns=(@cost*80)/100
GO

If I pass in a cost of 100.25 I get 20.05 80.20 in my query results but according to my break point i get 20 and 80. So any assistance will be greatly appreciated.

Regards.

Upvotes: 1

Views: 2702

Answers (6)

Dimitrios C. Psychias
Dimitrios C. Psychias

Reputation: 9

We have to aply two chnages

  1. In Our SQL server Table Field the field type must be Float or Numeric(N,M) where N= digits and M=decimals
  2. In our C# code we have to declare the SQLvalueType(SqlDbType.Float) and the DbType.Double.

In The following example i use a SQL Procedure by returning the Average of the 6 previous records , which I believe is useful and the c# main code to retrieve the field value.

/****** The SQL PROCEDURE ******/  

USE [PYTHION17]  
GO
/****** Object:  StoredProcedure [dbo].[P_GIP_AVG]    Script Date: 
25/1/2022 3:13:57 μμ ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO

ALTER PROCEDURE **`[dbo].[P_GIP_AVG]`**
@PF7 NVARCHAR(45), @PF1 NVARCHAR(5), @PF24 DATE, @PGip_avg float  OUTPUT

AS
BEGIN

SET NOCOUNT ON;

SELECT @PGip_avg = (SELECT  AVG(CAST(ST.ss AS Float)) AS Expr3
FROM         (
SELECT      TOP 6 f7, AVG(G_Goals) AS ss, f24
FROM         (SELECT      f7, g_goals, f24, f1
FROM         (SELECT      f7, G_Goals, f24, F1, row_number() OVER 
(PARTITION BY f7,F1
ORDER BY f24 DESC) AS rn
FROM  T_MAIN) AS sub
WHERE      rn < 7 AND f7 = @PF7 AND f1 = @PF1 AND F24 < @PF24 ) AS dt
GROUP BY f7, f24 ORDER BY F7 ) AS ST)

if @PGip_avg IS NULL 
begin
set  @PGip_avg = 0
end
return  @PGip_avg
END

The C# Code

public void P_Update_DataH(int P_aa, string PF1, DateTime PDATE, string 
PF7)
{
SqlCommand cmds = new SqlCommand("P_GIP_AVG", cons);
cmds.ResetCommandTimeout();
cmds.CommandTimeout = 0;
cmds.CommandType = CommandType.StoredProcedure;

cmds.Parameters.AddWithValue("@PF24", SqlDbType.Date).Value = PDATE;
cmds.Parameters.AddWithValue("@PF1", SqlDbType.Text).Value = PF1;
cmds.Parameters.AddWithValue("@PF7", SqlDbType.Text).Value = PF7;

**cmds.Parameters.AddWithValue("@PGip_avg", SqlDbType.Float);
cmds.Parameters("@PGip_avg").DbType = DbType.Double;**
*cmds.Parameters("@PGip_avg").Direction = ParameterDirection.Output;*

cons.Open();


cmds.ExecuteNonQuery();

**S_G_GOALS = FormatNumber(cmds.Parameters("@PGip_avg").Value, 2);**

try
{
    var sqlText = "UPDATE [CURRENT_DAY] Set  [G_GLS_MO] = @PS_G_GOALS WHERE 
[aa] = @aa ";
    using (var cnConnect = new SqlConnection(con.ConnectionString))
    {
        using (var cm = new SqlCommand(sqlText, cnConnect))
        {
            SqlCommand command = cons.CreateCommand();
            cnConnect.Open();
            cm.Parameters.AddWithValue("@aa", SqlDbType.Int).Value = P_aa;
            cm.Parameters.AddWithValue("@PS_G_GOALS", 
SqlDbType.Float).Value = S_G_GOALS;

            cm.ExecuteNonQuery();
            ii += 1;
            Console.WriteLine("    PDATE:  " + PDATE + "    " + S_G_GOALS);
            cnConnect.Close();
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine("  Exception Type: {0}" + "     ex.Message    " + 
ex.Message + "     ex.StackTrace    " + ex.StackTrace);
}
finally
{
    cons.Close();
}

return 1;
}

I hope I contributed in someway to this problem.

Thank you for your time.

Upvotes: 0

AquaAlex
AquaAlex

Reputation: 380

You need to use the FLOAT type. In VB.Net I do this: cmdSQL1.Parameters.Add(New SqlClient.SqlParameter("@VAT", SqlDbType.Float)).Direction = ParameterDirection.Output

Upvotes: 0

Niana
Niana

Reputation: 1047

I did not get the stored procedure to return my decimal place not matter what I tried.

So I did what I always knew I could do, I scrapped the stored procedure and just did the math in the code. Though I would like to know how I could solve that issue instead of doing a by pass but I couldn't figure it out.

Upvotes: 0

No Refunds No Returns
No Refunds No Returns

Reputation: 8336

This should work. Use decimal math instead of integer math.

SELECT @covByIndiv=(@cost*20.0)/100.0,@covByIns=(@cost*80.0)/100.0

Upvotes: 0

Nick
Nick

Reputation: 886

Per this forum post, it seems you will need to specify the precision and scale of your two output parameters, in .NET.

So try adding this: 

.Parameters("@covByIndiv").Precision=18
.Parameters("@covByIndiv").Scale=2

.Parameters("@covByIns").Precision=18
.Parameters("@covByIns").Scale=2

If that doesn't help, try changing your types from numeric(18,2) to decimal(18,2). Perhaps EF will handle the decimal type better.

Upvotes: 1

Tom Regan
Tom Regan

Reputation: 3841

Whenever you pass a decimal parameter to a SQL Server stored procedure you must set the Precision and Scale of the parameter, otherwise it defaults to scale of 0, which strips off the decimal portion. Your ObjectParameter class probably has Precision and Scale properties, or you can google "how to set precision and scale on ObjectParameter" and find proper means.

Upvotes: 1

Related Questions