如何创建一个 SQL Server 函数来返回一个 int？

How do I create a SQL Server function to return an int?

提问by DaveDev

I'm trying to create a SQL Function that tests whether a parameter starts with a certain term or contains the term, but doesn't start with it.

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

Basically, if the parameter starts with the term, the function returns a 0. Otherwise it returns a 1.

基本上，如果参数以术语开头，则函数返回 0。否则返回 1。

This is the bones of the function that I have, which I'm trying to adapt from another function I found:

这是我拥有的功能的骨架，我试图从我发现的另一个功能中进行调整：

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

回答by Alex K.

You don't provide a variable name for the return value, just its type, and the parens are not needed;

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

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

RETURNS int
AS
....

Also;

还;

select Data from @fieldName

Will not work, you would need dynamic SQLto select from an object the name of which is in a variable.

行不通，您需要动态 SQL从名称在变量中的对象中进行选择。

回答by Jon Egerton

There are a few problems here. I've added comments to the code below:

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

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

This should get you a bit closer to what you're trying to acheive.

这应该让你更接近你想要实现的目标。

回答by Jamiec

I'm trying to create a SQL Function that tests whether a parameter starts with a certain term or contains the term, but doesn't start with it.

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

Im assuming the following:

我假设以下内容：

• @fieldNameis in fact a table name (judging by your attempted usage).
• @searchtermis the term you're looking for
• Datais a column in table @fieldName

• @fieldName实际上是一个表名（根据您的尝试使用情况判断）。
• @searchterm是你要找的词
• Data是表中的一列 @fieldName

If any of the above are incorrect, this answer is neigh-on useless.

如果上述任何一项不正确，则此答案几乎没有用。

You will need to use dynamic sql as the table in a select query cannot be parameterised. You will need 2 different versions of the dynamic sql as you want to check "starts with" and more general "contains". You will need an output variable from the dynamic sql in order to determine the result of the call.

您将需要使用动态 sql，因为无法参数化选择查询中的表。您将需要 2 个不同版本的动态 sql，因为您要检查“开始于”和更一般的“包含”。您将需要来自动态 sql 的输出变量以确定调用的结果。

As an asside, INTis total overkill in terms of size. If you have just 2 states (which I doubt) you want BIT, if you have 3 states (as I suspect) you want TINYINT. I'll stick with int for now to stay close to your original example, but consider changing it.

顺便说一句，INT就规模而言，这完全是矫枉过正。如果你只有 2 个状态（我怀疑）你想要BIT，如果你有 3 个状态（我怀疑）你想要TINYINT。我现在会坚持使用 int 以接近您的原始示例，但请考虑更改它。

CREATE FUNCTION [dbo].[fnGetRelevance] 
(   
    @fieldName nvarchar(50),
    @searchTerm nvarchar(50)
)
RETURNS INT
AS
BEGIN

    DECLARE @startsWithResult INT,
            @containsResult INT
    DECLARE @startsWithSQL NVARCHAR(MAX) = N'SELECT @result=1 FROM ' + @fieldName + ' WHERE Data  LIKE '' + @searchTerm + '%'''
    DECLARE @containsSQL NVARCHAR(MAX) = N'SELECT @result=1 FROM ' + @fieldName + ' WHERE Data LIKE ''%' + @searchTerm + '%'''

   EXEC sp_ExecuteSQL @startsWithSQL, N'@result int output', @result = @startsWithResult OUTPUT

  IF @startsWithResult = 1
    RETURN 0

  EXEC sp_ExecuteSQL @containsSQL, N'@result int output', @result = @containsResult OUTPUT

  IF @containsResult = 1
    RETURN 1

END

回答by DaveDev

for reference, this is the complete function as implemented with the suggestions from Alex K

作为参考，这是按照 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