Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error Creating a Table valued Function

Status
Not open for further replies.

IanWaterman

Programmer
Jun 26, 2002
3,511
GB
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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top