problem returning decimal value from stored procedure

Question

Hi,
im not able to return decimal value from stored procedure, if im returning decimal then also it is giving integer value

ex:-
create procedure test
as
return 23.5

declare @i decimal(4,2)

exec @i = test
print @i

Im returning 23.5 but the value stored into variable @i is 23.00
Can anyone help

Answer 1

Here it is:

 

CREATE PROCEDURE p1

@value decimal(4,2) OUT

AS

SET @value = 23.5;

GO

 

DECLARE @v decimal(4,2);

EXEC p1 @v OUT;

PRINT @v;

 

 

Thanks,

Zuomin

 

Answer 2

I have the same problem with an output decimal parameter returning as an integer but I cannot understand  the solution  written in this post.

Could someone tell me what to do so that the decimal output  is returned properly.  I am using vb.net 2008 and sql 2005.

Thanks

Answer 3

Here is another sample for stored procedure decimal output.

Let us know if works for you.

-- Stored procedure with decimal data type output

USE AdventureWorks2008;

GO

CREATE PROCEDURE uspDecimalReturn

@Parm DECIMAL(36,2)  OUT

AS

SET @Parm = 9335535353535363464353523.77;

RETURN

GO

DECLARE  @decimal DECIMAL(36,2);

EXEC uspDecimalReturn

@decimal OUT;

SELECT sprocResult = @decimal;

GO

/*  Result

sprocResult

9335535353535363464353523.77

*/

-- Cleanup

DROP PROC uspDecimalReturn

GO

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 4

You can't return anything but an integer that way.  Period.  It's the way Microsoft implements function return values for t-sql stored procedures.  If you want to return anything but an integer, you have to use the more cumbersome syntax of output variables.  I agree, it would clean up a lot of code, but I doubt this is high priority for MS right now.

 

Answer 5

The best practice is to use the stored procedure return value to indicate success or failure rather than return data.  The defacto standard since the Sybase days has been to return zero for success and non-zero for error.  This is what the system stored procedures do.

 

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

When you are trying to get a Total of Decimal values from the database (Say the total of all the line items on an invoice).

So,

you are saying that best practice is to return only a Success or failure?   what kind of advice is that?

or are you saying that we should all buy Oracle if we want to deal in values?

or maybe I should put my program in a loop and pass it .01 and do a compare for success and when it fails bump it up to .02 and so on and so on.

I don't understand why you think that is helpful.

Now, for the original Question,

the problem is not with the stored procedure returning values, as you have seen in a couple of examples that running it from Mgt studio it works.  The issue is how do we setup .Net to interpret it correctly.

 command.Parameters["@OutCharges"].Direction = ParameterDirection.Output;

int rows = command.ExecuteNonquery;

decimal TotalChg = Convert.ToDecimal(command.Parameters["@OutCharges"].value);

the above 3 lines seem like they should work if the output of the stored procedure is a value defined as decimal(18,2), but it only returns the integer portion.





Answer 7

Daryl,

The "original question" Bhaskar asked was how to get a decimal value into @i from this statement: exec @i = test. The answer is that you can't. You need to use a procedure parameter, not capture the return value, which is not designed for this purpose.

You seem to have a different question. It's a good question, but it's not the original question of this thread. You should probably ask it in a separate thread for visibility, but I'll take a shot at why you're seeing this problem. Maybe you are not explicitly setting the Scale property of the @OutCharges parameter in .NET. See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.scale%28v=vs.110%29.aspx, specifically the "Caution" and the remark "Precision and Scale are required for output parameters."

If that's not it, please start a separate question thread instead of replying here.

Answer 8

the above 3 lines seem like they should work if the output of the stored procedure is a value defined as decimal(18,2), but it only returns the integer portion.

I suspect Steve is spot on with his diagnosis as usual.  Just like T-SQL, .NET defaults to a scale of zero when a scale is not specified so you need to set those properties:

command.Parameters["@OutCharges"].Precision = 18;
command.Parameters["@OutCharges"].Scale = 2;

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/