How can I write this Table Valued Function as a Stored Procedure?

Question

I have the following TVF for fulltext search:

FUNCTION [dbo].[Fishes_FullTextSearch]
(@searchtext nvarchar(4000), @limitcount int)
RETURNS TABLE
AS
RETURN 
SELECT * FROM Fishes
INNER JOIN CONTAINSTABLE(Fishes, *, @searchtext, @limitcount)
AS KEY_TBL ON Fishes.Id = KEY_TBL.[KEY]

When I'm using this TVF, it doesn't return a collection of objects of type Fish (which I want!) - instead LINQ creates a new result type which includes all "Fish" fields and the fields Key and Rank.

In another question, it was suggested that I rewrite this TVF into a stored procedure for it to return Fish objects only. Can someone help me do this please? Also, it needs to be ordered by Rank.

Edit: I need objects of type "Fish" only, without Key or Rank. Otherwise LINQ will create a new return type which I can't use easily with my existing code.

Thank you!

Answer 1

Sure - no problem:

CREATE PROCEDURE [dbo].[Fishes_FullTextSearch]
       @searchtext nvarchar(4000), 
       @limitcount int
AS 
  SELECT Fishes.*
  FROM dbo.Fishes
  INNER JOIN CONTAINSTABLE(Fishes, *, @searchtext, @limitcount) AS KEY_TBL 
      ON Fishes.Id = KEY_TBL.[KEY]

That should return the same results, as the result set from the stored procedure.

Answer 2

You mean like this?

CREATE PROCEDURE [dbo].[Fishes_FullTextSearch]
    @searchtext nvarchar(4000),
    @limitcount int
AS
SELECT Fishes.* FROM Fishes
  INNER JOIN CONTAINSTABLE(Fishes, *, @searchtext, @limitcount)
  AS KEY_TBL ON Fishes.Id = KEY_TBL.[KEY]
  ORDER BY KEY_TBL.[Rank]

Update: (following comments and update to question)

Added order by clause and restricted return values to be from Fishes only. Assumes that Rank is a field name on the aliased KEY_TBL.

Answer 3

Also, it needs to be ordered by Rank.

CREATE PROCEDURE [dbo].[Fishes_FullTextSearch]
@searchtext nvarchar(4000),
@limitcount int
AS

SELECT Fishes.* 
FROM Fishes
INNER JOIN CONTAINSTABLE(Fishes, *, @searchtext, @limitcount) AS KEY_TBL ON Fishes.Id = KEY_TBL.[KEY]
ORDER BY Fishes.Rank

EDIT Guided by comment.

Answer 4

Create Procedure dbo.Fishes_FullTextSearch
       @searchtext nvarchar(4000)
       , @limitcount int
AS 
  Select Fishes.Col1, Fishes.Col2, ...
  From dbo.Fishes
    Join ContainsTable(Fishes, *, @searchtext, @limitcount) AS FreeTextTable
      On Fishes.Id = FreeTextTable.Key
  Order By FreeTextTable.Rank

You shouldn't use Select * but instead should enumerate the columns you want. Thus, if you only want columns from the Fishes table, only specify columns from the Fishes table in the Select clause.

Answer 5

The simplest answer to your first question: change

SELECT * FROM Fishes

to

SELECT Fishes.* FROM Fishes