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.

Complete grammar for SQL 2000 and 2005?

I would like to build a TSQL parser and lexer for various reasons. One of them involves complex data import procedures where we need to ensure that columns are populated before they are used as data sources or criteria for other columns. There are actually many very cool and useful things that could be done with a good TSQL parser.

My question is this: are there any grammars available online for DDL, DML, whatever you call the DBCC statements, etc.? I should think Microsoft would have to have something like an EBNF for their own parsers; would it be possible to make them public?
No. The grammar for TSQL is not public information. Why does data import procedures need to look at the code? You can verify data post or pre or during processing or declaratively using foreign keys, constraints, indexed views etc.|||Declarative programming has limits that we have exceeded. Constraints simply won't catch all of the errors that can and have happened to us. The alternative to what I describe is painstaking checking of code, work that can and therefore should be done automatically.

Another use for the parser would be to build a little editor that provides intellisense and also shows better error messages, such as in cases where a column name is ambiguous (why not use intellisense to suggest one of the valid aliases/table names?). Usage of indexes in queries could be indicated by coloring of column names, extended properties of columns could be displayed using tooltips, etc. Basically, I have a desire to bring the sort of power of Visual Studio 2005 offers C# developers to SQL developers. It would be much easier to do this with a grammar from Microsoft, instead of building my own via trial and error. To be honest, I should think Microsoft would be in favor of such tools, as they would provide additional incentive for developers to use SQL Server.

|||

I agree that a parser will help for providing more productive environment. But I just do not see how it helps to check data purity for instance. In most cases, you will have to analyze complex procedural logic which may or may not be entirely on the server-side. So it is probably best to catch those at the source or destination.

Anyway, I don't know of any plans in the near future to release the grammar for TSQL or SQL implementation. If you are using ANSI SQL compatible coding standards then you can get the grammar for free on the internet. This should help you for the SQL part not TSQL syntax.

|||The data import process works like the following. I first preprocess a single text file I receive from a client and generate several tab-delimited files consumable by BCP. Along with the files, I create format files based on metadata. I run BCP, dumping the data in a primary table. After that, I run numerous scripts against the data to converting text codes to foreign keys, verify data adhere to specified formats, categorize data using fairly complex SQL statements, etc. Some statements depend on the values assigned by previous statements. Once the complexity of this interdependence gets to a certain level, it gets to be a pain to maintain. Verifying correctness would be trivial if I could parse SQL and see which fields are assigned to, which fields are assigned from, and which fields are used in criteria. The alternative is to manually recreate these three collections of fields.

The above is not the only use I have for TSQL parsing. Our current application is based of a metadata architecture used to describe databases so that our client has absolutely zero database-specific code. This involves, among other things, a query generation engine that probably equals if not exceeds the abilities of the .NET 3.5 Entity Framework. We now have two versions of the query generation engine: a VB6 one and a C# one. It would be invaluable to test the C# one by ensuring that it generates the same effective SQL as the VB6 one. I would rather not enforce character-for-character equality -- my time can be spent in better ways that completely reverse engineering the old algorithm so that the new one matches the old character-for-character. Being able to treat TSQL code as data, Lisp style, is the best solution.

I don't want to come off as sounding antagonistic, although it may be too late. I just don't understand why Microsoft wants to make my life (and by proxy, of many of their loyal developers) harder by not providing a completely derivable piece of data. I wouldn't be competing with them by having a full grammar; I would be enhancing one of their existing products, making it more lucrative. Surely Microsoft understands the power of treating code as data -- there is the CodeDom for generating .NET code, and the .NET GC was originally prototyped in Lisp, a language renowned for its treatment of code as data. DLINQ depends on Expression<T>, which can be generated by lambda functions, which are code converted to data.

I have a feeling all of the above is for nought. My impression is that Microsoft can be completely opaque if it wants and a little guy like me has no chance in getting any sort of insightful answer from them to a question like this. I would absolutely love to be wrong!

Microsoft understands how much of a big deal intellisense is.