Hi all,
I am having a terrible time with a stored procedure using SQL Server
2005. I think it has something to do with a divide by zero issue.
Here is my sproc:
ALTER
PROCEDURE [dbo].[GetPlayers]
@TeamID
int
AS
SELECT PlayerID,
TeamID,
FirstName
,
LastName
,
FirstName
+ ' ' + LastName As Name,
"SPct"
=
CASE
(SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4)
AND GameDetail.PlayerID = Player.PlayerID)
WHEN
0 THEN 0
ELSE
Cast
(
Cast
( (
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID =
Player.PlayerID AND AtBatID = '1') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID =
Player.PlayerID AND AtBatID = '2') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID =
Player.PlayerID AND AtBatID = '3') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID =
Player.PlayerID AND AtBatID = '4')
/
(SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4)
AND GameDetail.PlayerID = Player.PlayerID
) * 1.0
) As int
) As Decimal(4,3))
End
FROM
Player
WHERE
TeamID = @TeamID
ORDER
BY FirstName, LastName
Here is the error I get when I run this:
Msg 8115, Level 16, State 8, Procedure GetPlayers, Line 9
Arithmetic overflow error converting int to data type numeric.
So the results should look like: .333 or .500 or .000
It's driving me crazy. I have tried everything but cannot get the
desired results. I'm hoping someone can help me.
Thanks,
Bob
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNet2/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
