TVP with SP doesn't work - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

TVP with SP doesn't work

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 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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources