quote:

---

Originally posted by JacobPressures

Is it possible to use a TVP in a TVF?

I know atht Table Valued Parameters are read only. However I'm not modifying the TVP. I'm changing the results and placing them into a different table which is outputted by the Table Valued Fuction.

I'm getting error messages.

Here is the code. I don't know if the messages are due to the fact that I can't do this or if its because there is an error. Thanks.

CREATE TYPE ID2Analyze_type AS TABLE(ID INT NOT NULL PRIMARY KEY)
GO



CREATE PROC FUNCTION dbo.AccountNumberListAnalysis
@IDTable ID2Analyze_type READONLY,

RETURNS @AnalyzedTable TABLE (
[Type] VARCHAR(100) NOT NULL,
TotalCount INT NOT NULL,
Percentage INT NOT NULL
)
AS
BEGIN

-- Simply checks to make sure data is in the TVP (Alternatively @rowCnt is used in percentages)
DECLARE @rowCnt INT = (SELECT COUNT(*) FROM @IDTable);
IF @rowCnt <= 0
BEGIN
RAISERROR
(N'No data provided.'
11,
1
);
RETURN -1;
END

INSERT INTO @AnalyzedTable ([Type], TotalCount, Percentage)
SELECT dbo.MapAccountNumberFormats(ID)as [Type], COUNT(*) TotalCount, ((COUNT(*)*100)/@rowCnt) as Percentage
FROM @IDTable
GROUP BY dbo.MapAccountNumberFormats(ID)
ORDER BY COUNT(*) DESC

RETURN;
END
GO

---