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)
 TVP with SP doesn't work
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JacobPressures
Posting Yak Master

112 Posts

Posted - 01/17/2013 :  15:28:04  Show Profile  Reply with Quote
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

JacobPressures
Posting Yak Master

112 Posts

Posted - 01/17/2013 :  16:57:54  Show Profile  Reply with Quote
Ok the guess, the stupid user/developer just needed to add NULL as the first parameter. That seems to be working.
Go to Top of Page

JacobPressures
Posting Yak Master

112 Posts

Posted - 01/17/2013 :  18:10:18  Show Profile  Reply with Quote
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.
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.04 seconds. Powered By: Snitz Forums 2000