SQL Server function with parameters

Question

I am creating a SQL Server function

ALTER function [dbo].[GetAbnormalProductionHourFlag]
    (@startDate date, 
     @endDate date, 
     @employeeId integer, 
     @lowBand decimal, 
     @highBand decimal) 
returns integer
as
begin
    declare @flagCount integer

    set @flagCount = (select count(*) 
                      from AHFAB_vTimecardLines
                      where lmlActualStartTime >= @startDate
                        and (lmlActualEndTime < @endDate or lmlActualEndTime is null)
                        and lmlEmployeeID = @employeeId
                        and (jmoActualProductionHours < @lowBand or jmoActualProductionHours > @highBand))

    return @flagCount

and call the function

select dbo.GetAbnormalProductionHourFlag1('2017/02/01', '2017/02/17', 5124, 0.10, 3.00)

When I try to debug, for some reason the value of @lowBand on the watchlist SQL debugger is passed as 0, instead of 0.1

Is there a special way to pass decimal value parameters to a SQL Server function?

I am confused, I thought by putting decimal on parameters type, it should pass the correct value.

I would appreciate it if you could shed some light on what I miss or what I do wrong.

decimal defaults to 0 scale, so yes. try decimal(9,3)

SqlZim
– SqlZim

2017-02-16 21:11:06 +00:00
Commented Feb 16, 2017 at 21:11 — SqlZim
– SqlZim, Commented Feb 16, 2017 at 21:11

KekuSemau · Accepted Answer · 2017-02-16 21:12:34Z

1

The default for decimal is decimal(18,0), meaning 0 decimal digits. You have to give precision and scale explicitly like decimal(18,9).

This is described here on msdn.

answered Feb 16, 2017 at 21:12

KekuSemau

6,8434 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2018-11-13 23:26:50Z

Better yet, rewrite it as an inline table-valued function:

create function [dbo].[GetAbnormalProductionHourFlag_inline] (
    @startDate date
  , @endDate date
  , @employeeId integer
  , @lowBand decimal(19,6)
  , @highBand decimal(19,6)
) 
returns table as return (
  select FlagCount = count(*) 
  from AHFAB_vTimecardLines
  where lmlActualStartTime >= @startDate
    and (lmlActualEndTime < @endDate 
        or lmlActualEndTime is null)
    and lmlEmployeeID = @employeeId
    and (jmoActualProductionHours < @lowBand 
        or jmoActualProductionHours > @highBand)
);
go

select FlagCount 
from dbo.GetAbnormalProductionHourFlag_inline('2017/02/01', '2017/02/17', 5124, 0.10, 3.00)

reference:

Amir H. Bagheri · Accepted Answer · 2017-02-16 21:08:51Z

0

Redefine @lowBand as float.

ALTER function [dbo].[GetAbnormalProductionHourFlag]
(@startDate date, 
 @endDate date, 
 @employeeId integer, 
 @lowBand float, 
 @highBand float) 
 returns integer
 as
begin
declare @flagCount integer

set @flagCount = (select count(*) 
                  from AHFAB_vTimecardLines
                  where lmlActualStartTime >= @startDate
                    and (lmlActualEndTime < @endDate or lmlActualEndTime is null)
                    and lmlEmployeeID = @employeeId
                    and (jmoActualProductionHours < @lowBand or jmoActualProductionHours > @highBand))

return @flagCount

answered Feb 16, 2017 at 21:08

Amir H. Bagheri

1,4061 gold badge11 silver badges18 bronze badges

1 Comment

marc_s Over a year ago

float is inherently imprecise and tends to produce rounding errors etc. - I would recommend not to use float but decimal instead ....

Collectives™ on Stack Overflow

SQL Server function with parameters

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related