Hi,
I create a stored procedure to leverage FULLTEXTTABLE in SQL Server 2008R2. When I go to map the stored procedure to a typed view in the designer, the Reverse-engineer ResultSets to TypeView Definitions option is disabled. It was enabled originally and I was able to add a TypedView, then I went and modified my stored proc to add a new field. After refreshing the catalog, this option is now disabled. So I went back to the original stored proc and still it is disabled.
This is the procedure I am attempting to have a typed view for:
/****** Object: StoredProcedure [dbo].[sp_RelevantTransactionCategory] Script Date: 09/04/2010 16:48:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Me
-- Create date: 09-04-2010
-- Description: Uses a FULLTEXTTABLE search to find relevant transaction categories previously assinged to a transaction.
-- =============================================
CREATE PROCEDURE [dbo].[sp_RelevantTransactionCategory]
-- Add the parameters for the stored procedure here
@accountId INT = 0,
@searchString NVARCHAR(50) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @topRank int
SET @topRank =
(
SELECT MAX(kt.RANK)
FROM dbo.transaction_digest AS td
INNER JOIN FREETEXTTABLE(dbo.transaction_digest, [description], @searchString, 1) AS kt ON td.transaction_digest_id = kt.[KEY]
WHERE 2=2
AND td.account_id = @accountId
)
SELECT
td.Description
, tc.transaction_category_id
, kt.RANK
, (CAST(kt.RANK as DECIMAL)/@topRank) as rank_percent
FROM dbo.transaction_digest AS td
INNER JOIN FREETEXTTABLE(dbo.transaction_digest, [description], @searchString) AS kt ON td.transaction_digest_id = kt.[KEY]
INNER JOIN transaction_category tc on td.transaction_category_id = tc.transaction_category_id
WHERE 2=2
AND td.account_id = @accountId
ORDER BY kt.RANK DESC
END
GO
Using LLBLGen 3.0 August 18th, 2010
Thanks!