如何创建SQL Server函数以返回int？

Question

我正在尝试创建一个SQL函数来测试参数是以某个术语开头还是包含该术语，但不是以该术语开头。

基本上，如果参数以term开头，函数将返回0。否则，它将返回1。

这是我拥有的函数的框架，我正在尝试从我找到的另一个函数中改编：

CREATE FUNCTION [dbo].[fnGetRelevance] 
(   
    @fieldName nvarchar(50),
    @searchTerm nvarchar(50)
)

RETURNS @value int -- this is showing an error, it seems to expect table but all I want is an int
(
    -- does this need to be here? If so, what should it be?
)

AS

BEGIN

    declare @field TABLE(Data nvarchar(50))

    insert into @field
        select Data from @fieldName

    if (Data like @searchTerm + '%') -- starts with
    begin       
        return 0
    end
    else if (Data like '%' + @searchTerm + '%' and Data not like @searchTerm + '%') -- contains, but doesn't start with 
    begin       
        return 1
    end

END

GO

Answer 1

您不需要为返回值提供变量名，只需要提供它的类型，并且不需要括号；

CREATE FUNCTION [dbo].[fnGetRelevance] 
(   
    @fieldName nvarchar(50),
    @searchTerm nvarchar(50)
)

RETURNS int
AS
....

另外，还有；

select Data from @fieldName

将不起作用，您将需要dynamic SQL从名称在变量中的对象中进行选择。

Answer 2

这里有一些问题。我在下面的代码中添加了注释：

CREATE FUNCTION [dbo].[fnGetRelevance] 
(   
    @fieldName nvarchar(50),
    @searchTerm nvarchar(50)
)

RETURNS @value int --Returning an int is fine, but you don't need the @value variable
(
    --This isn't required (unless you're returning a table)
)

AS

BEGIN

    declare @field TABLE(Data nvarchar(50))

    --@fieldname is a varchar, not a table (is this where your error is coming from).     
    --If @fieldname is the name of a table you're going to need to exec a sql string and concat @fieldname into the string
    insert into @field
        select Data from @fieldName 

    --You need a variable to contain the value from Data 
    --(ie declare @Data and select @Data = Data)
    if (Data like @searchTerm + '%') -- starts with
    begin       
        return 0
    end
    else if (Data like '%' + @searchTerm + '%' and Data not like @searchTerm + '%') -- contains, but doesn't start with 
    begin       
        return 1
    end

END

这应该会让你更接近你想要获得的东西。

Answer 3

作为参考，这是根据Alex K的建议实现的完整函数

CREATE FUNCTION [dbo].[fnGetRelevance] 
(   
    @fieldName nvarchar(50),
    @searchTerm nvarchar(50)
)

RETURNS  int
AS
BEGIN
    if (@fieldName like @searchTerm + '%') -- starts with
    begin       
        return 0
    end
    else if ((@fieldName like '%' + @searchTerm + '%') and (@fieldName not like @searchTerm + '%')) -- contains, but doesn't start with 
    begin       
        return 1
    end

    return 1
END

GO