Strange Return in SQL Server

Source: Internet
Author: User
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.