Today, I helped my colleagues find bugs in a function. The source code for me is as follows: if exists (select * from dbo. sysobjects where id = object_id (n' [tb_Holiday] ') and OBJECTPROPERTY (id, n' isusertable') = 1)
Drop table [tb_Holiday]
GO
-- Define holiday table
Create table tb_Holiday (
HDate smalldatetime primary key clustered, -- holiday period
Name nvarchar (50) not null) -- holiday Name
GO
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [f_WorkDay] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [f_WorkDay]
GO
-- Calculate the number of working days between two dates
Create function f_WorkDay (
@ Dt_begin datetime, -- Calculation start date
@ Dt_end datetime -- end date of Calculation
) RETURNS int
AS
BEGIN
IF @ dt_begin> @ dt_end
RETURN (DATEDIFF (Day, @ dt_begin, @ dt_end)
+ 1 -(
Select count (*) FROM tb_Holiday
WHERE HDate BETWEEN @ dt_begin AND @ dt_end ))
RETURN (-(DATEDIFF (Day, @ dt_end, @ dt_begin)
+ 1 -(
Select count (*) FROM tb_Holiday
WHERE HDate BETWEEN @ dt_end AND @ dt_begin )))
END
GO
Execute the following query:
Select dbo. f_workday ('2017-01-01 ', '2017-02-01 ')
Select dbo. f_workday ('2017-02-01 ', '2017-01-01 ')
Different from ideal results.
Although there are logical errors, the main problem is not here. At that time, I doubted whether the actual statement execution was correct. A strange problem was found after the test. That is, whether in a function or stored procedure, adding the exact number and simple arithmetic computation after the return can make the function or stored procedure return immediately. However, if the return value contains a query statement, such as return (select count (*) from TableName), the function or stored procedure will continue to be executed. If no other return statement is followed, the returned values can still remember the returned values (return (select count (*) from TableName ). I couldn't explain it. I checked it online and found no reason. Is the expression after return not a subquery? See who knows the root cause.