computed columns and casting

Why does the following fail the parser ?
CREATE PROC [dbo].[GetQuestionAnswers]
@.QuestionID int
AS
DECLARE @.TotalCount int
SELECT @.TotalCount = Sum(AnswerCount)
FROM Answer
WHERE QuestionID = @.QuestionID
IF (@.TotalCount = 0)
BEGIN
SET @.TotalCount = 1
END
SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
AnswerFraction
FROM Answer
WHERE QuestionID = @.QuestionID
ORDER BY Rank
GODouble is not a Transact-SQL system data type. Use float or real for
approximate values or decimal for exact numeric values.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
> Why does the following fail the parser ?
> CREATE PROC [dbo].[GetQuestionAnswers]
> @.QuestionID int
> AS
> DECLARE @.TotalCount int
> SELECT @.TotalCount = Sum(AnswerCount)
> FROM Answer
> WHERE QuestionID = @.QuestionID
> IF (@.TotalCount = 0)
> BEGIN
> SET @.TotalCount = 1
> END
> SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
> AnswerFraction
> FROM Answer
> WHERE QuestionID = @.QuestionID
> ORDER BY Rank
> GO
>|||Hi Dan, and thanks for the response.
I should have phrased my question differently (because I quickly found out
that Double is not SQLDataType).
How to write SQL that creates a float or real or decimal psuedocolumn equal
to the quotient of two integer values (columns of integer type, or
otherwise) -- I do not want truncation to the nearest integer.
Thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
> Double is not a Transact-SQL system data type. Use float or real for
> approximate values or decimal for exact numeric values.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
>|||Use
CAST(AnswerCount / @.TotalCount AS Decimal(12,2))
Madhivanan|||John,
It will not work to cast after dividing. You need to cast or
force implicit conversion before dividing.
Won't work:
cast(AnswerCount / @.TotalCount as double precision)
Either of these will work:
cast(AnswerCount as double precision) / @.TotalCount
(1e0*AnswerCount)/@.TotalCount
Steve Kass
Drew University
John A Grandy wrote:

>Hi Dan, and thanks for the response.
>I should have phrased my question differently (because I quickly found out
>that Double is not SQLDataType).
>How to write SQL that creates a float or real or decimal psuedocolumn equal
>to the quotient of two integer values (columns of integer type, or
>otherwise) -- I do not want truncation to the nearest integer.
>Thanks.
>"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
>
>
>|||CREATE PROCEDURE GetQuestionAnswers ( @.my_question_id INTEGER)
AS
SELECT A1.answer_id, A1.answer_txt,
(A1.answer_count
/ (SELECT CASE WHEN SUM(A2.answer_count) = 0
THEN CAST (1.00 AS REAL)
ELSE SUM(A2.answer_count) END)
FROM Answers AS A2
WHERE A2.question_id = A1.question_id))
AS answer_fraction
FROM Answers AS A1
WHERE A1.question_id = @.my_question_id;
Untested; the CASE expression will resolve to the highest data type in
the THEN or ELSE clauses. This saves a local variable and lets the
optimizer do its thing.