CODEWITHSUNDEEP
sqlstored-procedures
dotnetdevcsharp
dotnetdevcsharp

Reputation: 3980

Rounding Down sql server 2008 r2

Any idea where the beelow would be rounding down the total salary for employee 11 is 10.44 but its rounding it down as 10 for some resaon even though their both set to decimals (8,2)

   ALTER procedure [dbo].[hms_GetEmployeeSallaryByEmpNo]
    (

    @Id int
   )
as
/* lets make our tempory tables */
create table #contracts
( 
empno INT,
contract_id INT
)

 DECLARE @contract_id int
 DECLARE @totalsallary decimal(8,2)
 begin 
  INSERT into #contracts SELECT c.emp_no,c.contract_id
  FROM  contract c
  where c.emp_no =  @Id AND c.leave_date='1900-01-01 00:00:00.000'
 end 

 SET @totalsallary =(select SUM(salary)
from #contracts c,salary s
where s.contract_id = c.contract_id)
return @totalsallary

Upvotes: 0

Views: 266

Answers (1)

D Stanley
D Stanley

Reputation: 152596

Stored Procedures can only return integer values:

From MSDN:

Stored procedures can return an integer value to a calling procedure or an application.

What you want is an output parameter:

ALTER procedure [dbo].[hms_GetEmployeeSalaryByEmpNo]
(
   @Id int,
   @totalsalary decimal(8,2) OUTPUT
)
...
SET @totalsalary =
(
    SELECT SUM(salary)
    FROM #contracts c,salary s
    WHERE s.contract_id = c.contract_id
)

--return @totalsalary  -- not necessary anymore

or a Scalar Value Function:

CREATE FUNCTION [dbo].[hms_GetEmployeeSalaryByEmpNo]
(
    @Id int
)
RETURNS DECIMAL(8,2)
AS
....
SET @totalsalary =
(
    SELECT SUM(salary)
    FROM #contracts c,salary s
    WHERE s.contract_id = c.contract_id
)

RETURN @totalsalary

*also note that I corrected the spelling of "salary" in my examples.

Upvotes: 1

Related Questions