SQL测试用户定义的函数

I have the following code. I am trying to test the function by passing in a @Hire_Date Datetime and seeing what value would be returned However I am new to SQL server 2012 and have no idea how I would go about doing this.

我有以下代码。我试图通过传入@Hire_Date日期时间来测试该函数，并查看将返回什么值但是我是SQL Server 2012的新手，并且不知道我将如何进行此操作。

Thanks.

谢谢。

CREATE FUNCTION dbo.getYearsOfService (@Hire_Date Datetime) 
RETURNS int
AS BEGIN

    DECLARE @thisYear int
    DECLARE @thisMonth int
    DECLARE @hireYear int
    DECLARE @hireMonth int
    DECLARE @yearlengthOfService int
    DECLARE @monthlengthOfService int

    SET @thisYear = YEAR(getdate())
    SET @thisMonth = MONTH(getdate())
    SET @hireYear = YEAR('@Hire_Date')
    SET @hireMonth = MONTH('@Hire_Date')

    SET @yearlengthOfService = (@thisYear - @hireYear);
    SET @monthlengthOfService = (@thisMonth - @hireMonth);  

    IF (@monthlengthOfService < 0)  
        SET @yearlengthOfService = (@yearlengthOfService - 1);

    RETURN @yearlengthOfService
END;

2 个解决方案

#1

You can just use SELECT:

你可以使用SELECT：

SELECT dbo.getYearsOfService(GETDATE())

要么

SELECT dbo.getYearsOfService(CAST('2021-01-20' as DATETIME))

(Perhaps the hire date of the next president of the U.S.)

（也许是下一任美国总统的雇用日期）

If you have a table of dates, you can use the value in the table as well:

如果您有一个日期表，您也可以使用表中的值：

SELECT dbo.getYearsOfService(datecol)
FROM t;

#2

You would just query the function like this:

你只需要查询这个函数：

SELECT dbo.getYearsOfService ('02/25/2017')
;

Or if you want to use the function against a record in a table, you would query it against a value from that row, like this:

或者，如果要对表中的记录使用该函数，则可以根据该行中的值查询该函数，如下所示：

SELECT dbo.getYearsOfService(<ColumnName>)
FROM <TableName>
WHERE <SomeColumn> = <SomeValue>
;

Or if you wanted to return values together with it, you could run it against the whole table (or part of it), like this:

或者如果你想与它一起返回值，你可以对整个表（或它的一部分）运行它，如下所示：

SELECT <Column1>, <Column2>, <Column3>, dbo.getYearsOfService(<ColumnName>) AS <WhateverNameYouWant>
FROM <TableName>
WHERE <SomeColumn> = <SomeValue> -- Optional WHERE Clause
;

NOTE:

注意：

Please notice that in the body of your function, you have an error:

请注意，在函数体中，您有一个错误：

SET @hireYear = YEAR('@Hire_Date')
SET @hireMonth = MONTH('@Hire_Date')

You don't want to pass strings here, but the parameter value. Do away with the apostrophes and you should get a clean run with no errors.

您不希望在此处传递字符串，而是传递参数值。撇开撇号，你应该得到一个没有错误的干净运行。

SET @hireYear = YEAR(@Hire_Date)
SET @hireMonth = MONTH(@Hire_Date)

#1