Error when converting data type decimal to numeric

Hello -

I am running LightSpeed 5, nightly build from 10/21 of this year, using VS 2013 and C# along with SQL Server Express 2008 R2. I ran into some rather strange behavior but I don't know if this is a known bug / behavior or not. Here is a brief summary of what's going on. If you want to see more or want to see a working example I can work on that in the next day or two. If you've already seen this, I saved myself some time... :)

I have a stored procedure that takes an int and a decimal as follows:

CREATEPROCEDURE [dbo].[gm_GetWorkerHours] 
    @WorkerID   INT,
    @HrsWorked  DECIMAL(6,2)    OUTPUT
AS
BEGIN
    SET @HrsWorked = dbo.TotalHoursWorked(@WorkerID);
END

When I call this from code I typically would call it like this:

            decimal hours = 0m;
            UnitOfWork.GetWorkerHours(workerID, ref hours);
            foreach (var record in records)
            {
                record.TotalHoursWorked = hours;
            }

However, when I do this I get the exception that serves as my subject line. I chased this for an hour or so before trying what eventually worked - if I initialize the ref variable to something other than 0, say 100.01, I don't get the error. In fact, the following init values produce the associated error or not:

1.1 -> error
0.01 -> error
1.01 -> error
10.01 -> no error
10 -> no error
100 -> no error

Any ideas? It's workign for now but something's not right here... Thanks, Dave

You need to ensure your precision/scale defined on that variable matches the proc definition. Unfortunately we dont set this automatically for you but you can sort this out in the designer. To fix this right click on the HrsWorked property and set the precision to 6 and the scale to 2 which matches the decimal declaration in your proc.

The problem is being caused by ADO.NET automatically determining these values from the value being passed in - e.g. 0 has a precision of 1 and scale of 0, however your output value clearly doesn't so this is where the SQL error comes from. This is also why you are seeing a variance in the behaviour depending on the original value of the variable when its passed in to the proc call.

Jeremy

That fixed it! Thanks, it makes perfect sense when you know what's going on...