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

Question

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.

Basically, if the parameter starts with the term, the function returns a 0. Otherwise it returns a 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

Looks like you're trying to return `BIT` (boolean) not `INT`. Albeit there is a 3rd state you're not currently handling; doesnt start with and doesnt contain `searchTerm`. — Jamiec, Jun 07 '11 at 11:33

score 12 · Accepted Answer · answered Jun 07 '11 at 11:26

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

score 3 · Answer 2 · answered Jun 07 '11 at 13:39

for reference, this is the complete function as implemented with the suggestions from 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

score 1 · Answer 3 · answered Jun 07 '11 at 11:31

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.

score 0 · Answer 4 · answered Jun 07 '11 at 11:50

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.

Im assuming the following:

@fieldName is in fact a table name (judging by your attempted usage).
@searchterm is the term you're looking for
Data is a column in table @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.

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.

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

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

4 Answers4