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

Posted by Alex on Stack Overflow See other posts from Stack Overflow or by Alex
Published on 2010-05-24T17:02:20Z Indexed on 2010/05/24 17:11 UTC
Read the original article Hit count: 173

Filed under:

sql

|

table-valued-function

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.

Thank you!

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about table-valued-function

Multi-statement Table Valued Function vs Inline Table Valued Function

as seen on Stack Overflow - Search for 'Stack Overflow'
ie: CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO versus: CREATE… >>> More
Entity Framework 4 Table Valued Function

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I am aware that table valued functions are not supported in previous versions of entity framework. I was wondering if this is now supported in EF 4? I cant see my functions in the edm designer so i'm guessing they are not supported unless I am doing something wrong? If they are not supported… >>> More
Table valued function only returns CLR error

as seen on Stack Overflow - Search for 'Stack Overflow'
I have read-only access to a database that was set up for a third-party, closed-source app. Once group of (hopefully) useful table functions only returns the error: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to… >>> More
How can I write this Table Valued Function as a Stored Procedure?

as seen on Stack Overflow - Search for 'Stack Overflow'
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… >>> More
Changing a table-valued function that a stored procedure calls is not recognized?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hey all I have a stored procedure sp that calls a table-valued function tvf. Sometimes I modify the tvf but when subsequently executing sp, the output from sp is the same as before the modification. It seems like it is cached or compiled or something. If I make some dummy change to the sp, then I… >>> More