Good afternoon!

Please help me!

I can not see what am I doing wrong!

I have the next CLR example:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

namespace Test
{
    public class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void Test
        (
            out SqlDecimal Out
        )
        {
            SqlCommand Comm = new SqlCommand();
            SqlParameter pOut = new SqlParameter("@Out", SqlDbType.Decimal);
            StringBuilder sb = new StringBuilder();
            pOut.Direction = ParameterDirection.Output;
            pOut.Value = (SqlDecimal)0.0;
            Comm.Parameters.Add(pOut);
            sb.AppendLine("SET @Out = 1;");
            //sb.AppendLine("SET @Out = CAST(1 AS [DECIMAL](15, 4));");
            Comm.CommandText = sb.ToString();
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                Comm.Connection = conn;
                conn.Open();
                Comm.ExecuteNonQuery();
                Out = (SqlDecimal)Comm.Parameters["@Out"].Value;
            }
        }
    }
}

And I implement it into SQL just this:

CREATE ASSEMBLY
 [Test]
FROM
 N'Z:\ROOTFILE\Test.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE PROCEDURE [dbo].[Test]
(
 @Out AS [DECIMAL](15, 4) OUTPUT
)
AS EXTERNAL NAME [Test].[Test.StoredProcedures].[Test]
GO

DECLARE @Out AS [DECIMAL](15, 4)
EXECUTE [dbo].[Test]
 @Out = @Out OUTPUT;
SELECT
 @Out
GO

--DROP PROCEDURE [dbo].[Test]
--GO

--DROP ASSEMBLY [Test]
--GO

But When I execute the procedure I obtain this error:

Mens 6522, Nivel 16, Estado 1, Procedimiento Test, Lnea 16
A .NET Framework error occurred during execution of user-defined routine or aggregate "Test":
System.Data.SqlClient.SqlException: Arithmetic overflow error converting int to data type numeric.
System.Data.SqlClient.SqlException:
   en System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   en System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
   en System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   en System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   en Test.StoredProcedures.Test(SqlDecimal& Out)

Even when I cast the number as DECIMAL(15, 4) I obtain an Arithmethic overflow and I can not see where is my mistake!

I am using SQL Server 2008 R2 And Visual Studio Express 2010.

Thank a lot for your valuable help.

Re

You have not specified any precision and scale for the parameter @Out. I don't know on the top of my head what defaults .NET applies, but they don't seem to fit. In any case, you should always specify precision and scale explicitly:

 SqlParameter pOut = new SqlParameter("@Out", SqlDbType.Decimal);
 pOut.Precision = 15;
 pOut.Scale = 4;

Try specifying the desired parameter precision and scale, and initialize the value with a new SqlDecimal structure:

pOut.Precision = 15;
pOut.Scale = 4;
pOut.Value = new SqlDecimal(0.0m);

Good afternoon!

Thanks a lot guys!

I did not know that!

That worked for me!

Thanks again!

Regards,

Good Afternoon Dan!

Why do you use an m letter inside new SqlDecimal parametter?

Thanks for your answer!

Regards,

Why do you use an m letter inside new SqlDecimal parametter?