|
JacobPressures
Posting Yak Master
108 Posts |
 Posted - 01/17/2013 : 15:28:04
|
This thing used to work. But now I can't get this stupid thing to work. I guess its related to something the developer (me)is doing that is stupid.
The SP takes one value via an optional parameter or a list of values via a TVP.
But i keep getting this error:
Msg 206, Level 16, State 2, Procedure AccountNumberAnalysis, Line 0
Operand type clash: AccNumber_type is incompatible with int
CREATE TYPE AccNumber_type AS TABLE(AccNumber INT NOT NULL PRIMARY KEY)
GO
--exec dbo.AccountNumberAnalysis 1555
DECLARE @NumberList AccNumber_type
INSERT INTO @NumberList (AccNumber) VAlUES (1555)
--SELECT * FROM @NumberList
exec dbo.AccountNumberAnalysis @NumberList;
GO
ALTER PROC dbo.AccountNumberAnalysis
@PPINumber INT = NULL,
@PPINumberList AccNumber_type READONLY
AS
BEGIN
DECLARE @rowCnt INT = (SELECT COUNT(*) FROM @PPINumberList);
IF @rowCnt <= 0
BEGIN -- If TVP is empty then try this optional single parameter
SELECT dbo.MapAccountNumberFormats(EffAcctNum) [Type], COUNT(*) TotalCount, LEN(dbo.MapAccountNumberFormats(EffAcctNum)) TypeLength,CAST(CAST((CAST(COUNT(*) AS DECIMAL(18,0))*100)/CAST((SELECT COUNT(*) FROM dbo.[Claim_Encounter] With (NoLock) WHERE PPInumber = @PPINumber) AS DECIMAL(18,0)) AS DECIMAL(5,2)) AS VARCHAR(5)) + '%' as Percentage
FROM analytics.dbo.[Claim_Encounter] WITH (NOLOCK)
WHERE PPInumber = @PPINumber OR @PPINumber IS NULL
GROUP BY dbo.MapAccountNumberFormats(EffAcctNum)
ORDER BY COUNT(*) DESC
END
ELSE
BEGIN -- If TVP is not empty, ignores optional single parameter above and uses TVP below
SELECT dbo.MapAccountNumberFormats(EffAcctNum) [Type],COUNT(*) TotalCount, LEN(dbo.MapAccountNumberFormats(EffAcctNum)) TypeLength, CAST( CAST(((CAST(COUNT(*) AS DECIMAL(18,0))*100)/CAST(@rowCnt AS DECIMAL(18,0))) AS DECIMAL(5,2)) AS VARCHAR(5)) + '%' as Percentage
FROM analytics.dbo.[Claim_Encounter] WITH (NOLOCK)
WHERE PPInumber IN (SELECT PPInumber FROM @PPINumberList)
GROUP BY dbo.MapAccountNumberFormats(EffAcctNum)
ORDER BY COUNT(*) DESC
END
END
GO
|
Edited by - JacobPressures on 01/17/2013 16:49:12
|
|
TVP with SP doesn't work
