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
– Jamiec, Commented Jun 7, 2011 at 11:33

Alex K. · Accepted Answer · 2011-06-07 11:26:47Z

13

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.

answered Jun 7, 2011 at 11:26

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DaveDev · Accepted Answer · 2011-06-07 13:39:10Z

4

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

answered Jun 7, 2011 at 13:39

DaveDev

42.4k73 gold badges235 silver badges395 bronze badges

Comments

Jon Egerton · Accepted Answer · 2011-06-07 11:31:13Z

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.

Jamiec · Accepted Answer · 2011-06-07 11:50:55Z

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

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related