Use stored procedure output parameter

Question

ALTER PROCEDURE dbo.StoredProcedure8
@emp_code bigint,
@co_id bigint,
@p decimal(8,2) output

AS

SELECT @p = (select sum(tran_value) from emp_ded_ben_trans where emp_code=@emp_code and co_id=@co_id and period_flg=2 and tax_flg=0)

RETURN  

Answer 1

To call that sproc and retrieve the output parameter, you do (e.g.):

DECLARE @p DECIMAL(8,2)
EXECUTE dbo.StoredProcedure8 123, 456, @p OUTPUT
-- @p now contains the output value

Update:

You don't need to use RETURN - you are right in that a RETURN can only return an INTEGER. But a return value is different to an OUTPUT parameter which is what you are actually using.

i.e. to get a RETURN value from a sproc, is different syntax:

DECLARE @Result INTEGER
EXECUTE @Result = SomeSproc

Answer 2

First you can use not a Stored Procedure by a Function if you have to return a single value basing on input parameters

If your sp return decimal when you need integer - just cast: SELECT (CAST @d AS INT) but this is very dangerous (possible type overflow)

Answer 3

Stored procedures aren't made to "return values" - that's what you have stored functions for.

CREATE FUNCTION dbo.CalculateSomething
  (@emp_code bigint, @co_id bigint)
RETURNS DECIMAL(8, 2)
AS BEGIN    
    RETURN
      (SELECT SUM(tran_value) 
       FROM dbo.emp_ded_ben_trans 
       WHERE 
          emp_code = @emp_code AND co_id = @co_id  
          AND period_flg = 2  AND tax_flg = 0)
END

You can then call this stored function like this:

SELECT dbo.CalculateSomething(value_for_emp_code, value_for_co_id)

and get back a DECIMAL(8,2) from the calculation.

Stored procedures will return the number of rows affected by their operation - an INT.

If you need to return a value from a stored proc, you need to use the OUTPUT parameter type and use the technique that AdaTheDev shows - you need to grab the output value into a variable.

Answer 4

If your Column tran_value is of Decimal Type, the @p will have decimal values after you run the query...

Create Table #test
(ID1 Int,
ID2 Decimal(8,2)
)

Insert into #test Values (1,1.1)
Insert into #test Values (2,2.2)
Insert into #test Values (3,3.3)

Declare @p Decimal(8,2), @intp int

Select @intp = Sum(ID1), @p = Sum(ID2) from #test

Select @intp as IntegerSum, @p as DecimalSum

Drop Table #test

Output

IntegerSum  DecimalSum
----------- ---------------------------------------
6           6.60

Note : You need not have to do anything specific to return the value from Stored Procedure through output parameter... Just assign the value to output parameter inside your SP, it will be returned to the caller automatically.

This means your SP is correct even without the return statement