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-17 : 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 0Operand type clash: AccNumber_type is incompatible with intCREATE TYPE AccNumber_type AS TABLE(AccNumber INT NOT NULL PRIMARY KEY)GO--exec dbo.AccountNumberAnalysis 1555DECLARE @NumberList AccNumber_typeINSERT INTO @NumberList (AccNumber) VAlUES (1555)--SELECT * FROM @NumberList exec dbo.AccountNumberAnalysis @NumberList;GOALTER PROC dbo.AccountNumberAnalysis@PPINumber INT = NULL, @PPINumberList AccNumber_type READONLY ASBEGINDECLARE @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 ENDELSE 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 ENDENDGO |
|
JacobPressures
Posting Yak Master
112 Posts |
Posted - 2013-01-17 : 16:57:54
|
Ok the guess, the stupid user/developer just needed to add NULL as the first parameter. That seems to be working. |
|
|
JacobPressures
Posting Yak Master
112 Posts |
Posted - 2013-01-17 : 18:10:18
|
Its working what i was doing originally was supplying the parameter variable name in the calling list. But without these variables being specified Sql Server didn't know what which one to apply it to. |
|
|
|
|
|
|
|