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

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.

基本上，如果参数以term开头，则函数返回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

4 个解决方案

#1

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

您不为返回值提供变量名称，只提供其类型，并且不需要parens;

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 SQL to select from an object the name of which is in a variable.

无法工作，您需要动态SQL从名称中包含变量的对象中进行选择。

#2

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.

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

#3

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:

我假设以下内容：

@fieldName is in fact a table name (judging by your attempted usage).
@fieldName实际上是一个表名（根据您的尝试使用情况判断）。
@searchterm is the term you're looking for
@searchterm是你正在寻找的术语
Data is a column in table @fieldName
数据是表@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，因为select查询中的表不能参数化。您需要2个不同版本的动态SQL，因为您要检查“开头”和更一般的“包含”。您将需要动态sql的输出变量以确定调用的结果。

As an asside, INT is 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

#4

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

#1