returning decimal from a stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

returning decimal from a stored proc

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-16 : 09:47:13

vb writes "I am trying return the percentage from a stored procedure. I am not able to return the decimal. It always returns an integer.

here is the stored proc(AdventureWorks DB in SQL 2005):
CREATE PROCEDURE [dbo].[getPercentage]
-- Add the parameters for the stored procedure here
@dept as int
,@retvalue as decimal output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @countDeptEmp as decimal
Declare @countTotEmp as decimal

SELECT @countDeptEmp = count(HumanResources.Employee.EmployeeID) --, HumanResources.EmployeeDepartmentHistory.DepartmentID
FROM HumanResources.Employee INNER JOIN
HumanResources.EmployeeDepartmentHistory ON
HumanResources.Employee.EmployeeID = HumanResources.EmployeeDepartmentHistory.EmployeeID
WHERE HumanResources.EmployeeDepartmentHistory.DepartmentID = @dept

SELECT @countTotEmp = count(HumanResources.Employee.EmployeeID) --, HumanResources.EmployeeDepartmentHistory.DepartmentID
FROM HumanResources.Employee

--SELECT @countDeptEmp
--SELECT @countTotEmp
SELECT cast( @countDeptEmp as decimal) / cast(@countTotEmp as decimal)
-- this displays the correct value on the ui.

SET @retvalue = cast( @countDeptEmp as decimal) / cast(@countTotEmp as decimal)
END

to execute the stored proc I am using

DECLARE @retvalue as decimal
SET @retvalue = 0
exec getPercentage 1,@retvalue Output
Select @retvalue

I get a value of 0 instead of .0241

Is it possible to return a value other than an numeric type?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-11-16 : 09:49:57

You must declare a decimal type with precision and scale in order to get decimal places:

DECLARE @retvalue as decimal(6,4)
SET @retvalue = cast( @countDeptEmp as decimal(6,4)) / cast(@countTotEmp as decimal(6,4))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 09:54:18

SELECT 1.0 * @countDeptEmp / @countTotEmp
-- this displays the correct value on the ui.

SET @retvalue = 1.0 * @countDeptEmp / @countTotEmp

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources