title: How do I create a SQL Server function to return an int?
body: <p>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.</p>
  
  <p>Basically, if the parameter starts with the term, the function returns a 0. Otherwise it returns a 1.</p>
  
  <p>This is the bones of the function that I have, which I'm trying to adapt from another function I found:</p>
  
  <pre><code>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
  </code></pre>