Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
JacobPressures
Posting Yak Master
112 Posts |
Posted - 2013-01-10 : 12:39:19
|
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)GOCREATE PROC dbo.AccountNumberListAnalysis@IDTable ID2Analyze_type READONLY, RETURNS @AnalyzedTable TABLE ( [Type] VARCHAR(100) NOT NULL, TotalCount INT NOT NULL, Percentage INT NOT NULL) ASBEGIN-- 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 <= 0BEGIN RAISERROR (N'No data provided.' 11, 1 ); RETURN -1;ENDINSERT INTO @AnalyzedTable ([Type], TotalCount, Percentage)SELECT dbo.MapAccountNumberFormats(ID)as [Type], COUNT(*) TotalCount, ((COUNT(*)*100)/@rowCnt) as PercentageFROM @IDTableGROUP BY dbo.MapAccountNumberFormats(ID) ORDER BY COUNT(*) DESCRETURN;ENDGO |
|
JacobPressures
Posting Yak Master
112 Posts |
Posted - 2013-01-10 : 12:53:50
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-10 : 22:56:05
|
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)GOCREATE PROC FUNCTION dbo.AccountNumberListAnalysis@IDTable ID2Analyze_type READONLY, RETURNS @AnalyzedTable TABLE ( [Type] VARCHAR(100) NOT NULL, TotalCount INT NOT NULL, Percentage INT NOT NULL) ASBEGIN-- 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 <= 0BEGIN RAISERROR (N'No data provided.' 11, 1 ); RETURN -1;ENDINSERT INTO @AnalyzedTable ([Type], TotalCount, Percentage)SELECT dbo.MapAccountNumberFormats(ID)as [Type], COUNT(*) TotalCount, ((COUNT(*)*100)/@rowCnt) as PercentageFROM @IDTableGROUP BY dbo.MapAccountNumberFormats(ID) ORDER BY COUNT(*) DESCRETURN;ENDGO
it should be a function in way you've writtenmake this change on top------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|