Error Creating a Table valued Function

[IanWaterman]

I am trying to create TVF with an Index. However, I get the error

Msg 444, Level 16, State 2, Procedure FN_BFS_TransactionTypeGroups, Line 29 [Batch Start Line 0]
Select statements included within a function cannot return data to a client.

Line 29 is where the With Statement is (see below), query from With to final select works fine, I just can't get the function to compile.
I have tried to create with and without a Parameter for the function. I don't need a parameter hence empty.

Alternatively, we currently use a view which is slow and cumbersome. I wanted to replace with a function with an index. If there is a better way I am happy to try that too. Prefer not to use a Stored Proc as that would have a much bigger impact on replacing view in multiple other SPs.

Any Suggestions
Thank you
Ian

CREATE FUNCTION [dbo].[FN_BFS_TransactionTypeGroups]()

RETURNS
@Data TABLE (

GroupName NVARCHAR(17),
SubGroup NVARCHAR(100), --at @ClosingDate
SubGroupCR NVARCHAR(100),
SubGroupCreditItemRec NVARCHAR(100),
CorD INT,
ID INT,
BASECODE INT,
STANDARDCODE INT,
VARIATIONCODE INT,
BSCode NVARCHAR(9),
BSVCode NVARCHAR(14),
TransactionLevel INT,
Comments DECIMAL(10,0),
[Description] DECIMAL(10,0),
Abbreviation NVARCHAR(3),
VariationUsageIndicator NVARCHAR(4),
TransDescription NVARCHAR(255)
,PKID INT PRIMARY KEY NOT NULL
)

AS
BEGIN

with Main_cte as
(
SELECT ....
)
Select *
from main_cte

RETURN;
END
GO

 

[IanWaterman]

Got it working I missed the
INSERT INTO @Data
prior to the final select.

As I understand it by populating
,PKID INT PRIMARY KEY NOT NULL
this creates an Implicit clustered index.

However, the data will join on the ID field. I could not use this as my primary key as there are strange business requirements which require this to be duplicated from the source data. Is there any way I can force a non unique index on this field too?

Thank you
Ian

 

[SgtJarrow]

Just a thought...have you considered just adding an INDEX to your existing VIEW that is slow and cumbersome?

Create Indexed Views

Robert "Wizard" Johnson III
U.S. Military Vets MC
Data Integration Engineer

 

[IanWaterman]

Yes we have, but design is flawed. So needed to be rebuilt anyway.

I was under the opinion that functions were more flexible in their design that Views. And we could add indexes more easily.

Ian