SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table Valued Function (TVF) or TVP Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JacobPressures
Posting Yak Master

112 Posts

Posted - 01/10/2013 :  12:39:19  Show Profile  Reply with Quote
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 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

JacobPressures
Posting Yak Master

112 Posts

Posted - 01/10/2013 :  12:53:50  Show Profile  Reply with Quote
I'm starting to see my error. For one I'm using Create Proc instead of Create Function.

Also I have a RETURN -1 which can't be returned if its a TVF.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2013 :  22:56:05  Show Profile  Reply with Quote
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


it should be a function in way you've written
make this change on top

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000