Tutorial :stored procedure return .00 decimal



Question:

this stored procedure does not return salary with decimal format 00.00

ALTER PROCEDURE taxable_varsalary    @emp_code bigint,  @co_id bigint          AS  declare @t_varsalary decimal(8,2)    set @t_varsalary  = (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 @t_varsalary   


Solution:1

ALTER PROCEDURE taxable_varsalary     @emp_code bigint,   @co_id bigint,   @t_varsalary decimal(8,2) OUTPUT    AS       select @t_varsalary  = 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  

The return value for a stored procedure has to be an int. Use an output parameter instead or select it


Solution:2

A stored procedure return only returns an integer, either use an OUTPUT parameter or do a SELECT at the end


Solution:3

Create a Scalar-Valued Function:

CREATE FUNCTION taxable_varsalary      @emp_code bigint,      @co_id bigint  RETURNS DECIMAL(8,2)  AS  declare @t_varsalary decimal(8,2)    set @t_varsalary  = (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 @t_varsalary   


Solution:4

Check below with OUTPUT:

ALTER PROCEDURE taxable_varsalary    @emp_code bigint,  @co_id bigint          AS  BEGIN  declare @t_varsalary MONEY    set @t_varsalary  = (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)    SELECT @t_varsalary OUTPUT    END  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »