CODEWITHSUNDEEP
sql-server
user7293420
user7293420

Reputation: 33

SQL Server function with parameters

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.

Upvotes: 0

Views: 101

Answers (3)

SqlZim
SqlZim

Reputation: 38063

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:

Upvotes: 1

KekuSemau
KekuSemau

Reputation: 6852

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.

Upvotes: 1

Amir H. Bagheri
Amir H. Bagheri

Reputation: 1416

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

Upvotes: 0

Related Questions