IanWaterman
Programmer
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
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