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

Question

I am trying to create an SQL function that checks if a parameter begins with a specific term or contains this term, but does not start with it.

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

These are the bones of the function that I have, which I am trying to adapt from another function that 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 

Answer 1

You do not specify a variable name for the return value, only its type, and pairs 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 will need dynamic SQL to select from the object whose name is in the variable.

Answer 2

There are several issues here. I 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 help you get a little closer to what you are trying to achieve.

Answer 3

I am trying to create an SQL function that checks if a parameter starts with a specific term or contains but does not start with it.

Im accepting the following:

• @fieldName is actually the name of the table (judging by your attempt to use).
• @searchterm is the term you are looking for
• Data - column in the @fieldName table

If any of the above rules is incorrect, this answer is useless.

You will need to use dynamic sql, since the table in the selected query cannot be parameterized. You will need two different versions of dynamic sql that you want to check for "starts with" and more general "contains". To determine the result of the call, you need an output variable from dynamic sql.

INT is said to represent a complete excess in size. If you have only 2 states (which I doubt), you want BIT , if you have 3 states (as I suspect), you want TINYINT . I will stick with int in order to stay close to your original example, but consider changing.

 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 

Answer 4

for reference, this is a complete function implemented with offers 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