August 13, 2012 at 11:27 am
CTEs don't start with a semicolon. No need for one before a WITH (responding to Joe's comment). The problem isn't a semicolon before WITH, the problem is that WITH is overloaded. If it's at the beginning of the statement, it's starting a CTE; otherwise it could be a query hint or other action at the end of the prior statement.
The plan is to require explicit termination on T-SQL statements in the future, which will solve the immediate issue. Won't handle the overload on WITH, but will at least make it clear that semicolons end statements.
Of course, even in a situation where each statement has to be terminated, it's possible to put the terminator at the beginning of each following statement, since whitespace will still be ignored. Of course, you can do the same thing if you really want to mess up C# devs. Put your begin-block mark closer to the end of the preceeding statement and your end-block closer to a following statement, and watch people go cross-eyed on it.
I disagree with the statement that "date", as a datatype, should never be used as a column name. In a calendar table, it makes total sense to have a Date column, quite likely even to have the Date column be date datatype. You can call it CalendarDate, or some such goofiness, but dbo.Calendar.CalendarDate is a bit redundant.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 2:29 pm
CELKO (8/13/2012)
I disagree with the statement that "date", as a datatype, should never be used as a column name. In a calendar table, it makes total sense to have a Date column, quite likely even to have the Date column be date datatype. You can call it CalendarDate, or some such goofiness, but dbo.Calendar.CalendarDate is a bit redundant.
well, cal_date follows ISO-11179 rules, and lets us know that we are flookign at the Common Era ISO-8601 daters, not the week-within-year date , ordinal date, Julian calendar, Jewish, Moslem, or Chinese dates
Not really.
If you need to be explicit about that, then DateCE would be a better column name. Or name the table CalendarCE and use Date as the column name, if you won't be including any other date standard in the table. Depends on what you want to do with the table. If you need multiple calendars in it, and won't be doing that kind of localization in a higher level of the software, then DateCE would make sense, along with another column named DateJulian, etc. But really, that kind of thing should be done in the UI layer, not the database, just like any other formatting. And your data dictionary should care for more details, like what day of the week you consider "1", what date you consider "0" (might not be the default 1/1/1900 in some uses), and so on.
After all, "cal_date" just means "calendar date". Unless it's something about dating someone named Calvin, I guess. But which calendar? It doesn't say that any more clearly than "Date". After all, it's in a table named "Calendar", so "Calendar.Cal_Date" is just redundant, and not any more explicit than "Calendar.Date". It still doesn't say anything about Julian, Mayan, Common Era, Astronomical, Chinese, et al. If you need that in the column name, by all means include it, but if you handle that in the UI, then redundancy is not a virtue.
P.S.: Joe, a suggestion: Spellcheck.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 3:18 pm
GSquared (8/13/2012)
CTEs don't start with a semicolon.
I disagree. While I'm sure that the intention is that the statement before must end with a semicolon, implementation and general practice clearly indicate that CTEs start with a semicolon. Specifically, there is one case where a semicolon is required in the middle of a statement rather than the end. That statement is the TRY/CATCH block. BEGIN TRY is not a valid statement all by itself. You get an error message if you try to execute a batch consisting of the single line "BEGIN TRY". Microsoft provides the syntax for a TRY/CATCH block as follows
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[ ; ]
Notice, in particular, the specification of the optional semicolon after the END CATCH, but nowhere else in the statement. Yet, when you try to run the following code, you get an error about a missing semicolon.
BEGIN TRY
WITH CTE(n) AS ( SELECT 1 )
SELECT *, GETDATE()
FROM CTE
END TRY
BEGIN CATCH
END CATCH
Clearly the semicolon is not terminating the TRY/CATCH block, so it must mark the beginning of the CTE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 13, 2012 at 3:35 pm
drew.allen (8/13/2012)
GSquared (8/13/2012)
CTEs don't start with a semicolon.I disagree. While I'm sure that the intention is that the statement before must end with a semicolon, implementation and general practice clearly indicate that CTEs start with a semicolon. Specifically, there is one case where a semicolon is required in the middle of a statement rather than the end. That statement is the TRY/CATCH block. BEGIN TRY is not a valid statement all by itself. You get an error message if you try to execute a batch consisting of the single line "BEGIN TRY". Microsoft provides the syntax for a TRY/CATCH block as follows
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[ ; ]
Notice, in particular, the specification of the optional semicolon after the END CATCH, but nowhere else in the statement. Yet, when you try to run the following code, you get an error about a missing semicolon.
BEGIN TRY
WITH CTE(n) AS ( SELECT 1 )
SELECT *, GETDATE()
FROM CTE
END TRY
BEGIN CATCH
END CATCH
Clearly the semicolon is not terminating the TRY/CATCH block, so it must mark the beginning of the CTE.
Drew
Interesting. However, it is not an error to have BEGIN TRY;. I'm thinking that this may be an issue related to BEGIN TRY/ CATCH. If you simply make it a BEGIN END block, the query works with no leading semicolon for the CTE.
BEGIN
WITH CTE(n) AS ( SELECT 1 )
SELECT *, GETDATE()
FROM CTE;
END
Also, the following works just fine, so the BEGIN must terminate the previous statement:
SELECT GETDATE()
BEGIN
WITH CTE(n) AS ( SELECT 1 )
SELECT *, GETDATE()
FROM CTE;
END
The following works as well:
BEGIN TRY
BEGIN
WITH CTE(n) AS ( SELECT 1 )
SELECT *, GETDATE()
FROM CTE;
END
END TRY
BEGIN CATCH
END CATCH;
August 13, 2012 at 3:46 pm
davidandrews13 (8/10/2012)
there's an answer at the bottom of this pagehttp://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx
it states:
Allowing an extra comma after the last column definition makes the code easier to generate and maintain because all lines can end in a comma, instead of all lines except the last one. $0In C and C# this same pattern appears in several places e.g. C#'s object initializer syntax.
They are not consistant on this when it comes to declared tables.
The following code produces an error in SQL Server 2005 and 2008 R2 when declaring a table:
declare @x table (
n1 int not null,
n2 int not null,
)
While this works fine for creating a table:
create table #x (
n1 int not null,
n2 int not null,
)
August 13, 2012 at 5:59 pm
But you know what doesnt work (i guess it is related to the original variable posting....)
Table Variables
Doesnt like the last comma:
DECLARE @var_TableVar TABLE ([Id] INT NULL, [Col1] INT NULL,)
Works just fine:
CREATE TABLE TestTable(
[Id] INT NULL,
[Col1] INT NULL,)
August 14, 2012 at 7:04 am
CELKO (8/13/2012)
if you need to be explicit about that, then DateCE would be a better column name. [] Or name the table CalendarCE and use Date as the column name
The point of the ISO-11179 rules is that a data element has a name that never depend on context. Furthermore the format is <attribute>_< attribute property> and _date is a property, a temporal scale Since there is only one ISO Standard calendar, I feel that "cal_" is a good name.
I left out the most alternate calendar --Fiscal!
.. But really, that kind of thing should be done in the UI layer, not the database, just like any other formatting.
This is not display formatting. It is a change of scale to another unit of measurement. This is an auxiliary table problem, not a dispaly problem.
And your data dictionary should care for more details, like what day of the week you consider "1",
1= monday; ISO Standard
what date you consider "0" (might not be the default 1/1/1900 in some uses)
'0001-01-01' to '9999-12-31' as per ISO-8601.
P.S.: Joe, a suggestion: Spellcheck.
Agreed, but I also need eye drops and some rest. I am recovering from eye surgery and it is a bitch to type when you are almost blind 🙂
Ouch on the eye surgery. Get well soon, and best wishes on solving whatever it was intended for.
On the first point, in that case, I consider the ISO standard horribly flawed, because it violates about 10,000 years of human study and thought on the subject of data, knowledge, et al. ALL data is completely dependent on context. There is no such thing as an isolated datum. If there were, it would be completely useless for anything.
It's also flawed logic. If the only valid calendar is the ISO one, and the word "date" means "day, month, and year: a phrase or string of numbers that denotes a specific day of the month or year. It usually consists of the name or number of the month, the number of the day, and the number of the year." (ref: http://www.bing.com/Dictionary/search?q=define+date&qpvt=definition+date&FORM=DTPDIA), then there is no need to indicate that a date is a calendar reference, no need to indicate which calendar it is a reference to, and thus still no actual need to prefix it. I've checked, and the ISO argument is just sloppy logic. It's a tautology, which means it's pointless to specify.
So, by the ISO standard, calling something a Date already completely defines it, thus negating the need to redundantly overdefine it.
Honestly, any standard that enforces tautologies, redundancies, and paradoxes, as the ISO standard does, is horribly flawed.
By that same standard, if you needed to record the spatial dimensions of a sheet of paper, you'd have to have columns like, "spacial_width", but you wouldn't include the unit-of-measure in the name. So, if the value is 11, is that inches, meters, angstroms, parsecs, rods, cubits, ... ? But a standard that calls for "calendar_date", but disallows "DateCE", would, for consistency, require "spacial_width", and not allow "WidthInches".
Hence, it's a seriously flawed standard. I prefer something that actualy communicates to human beings. I've never had anyone yet actually get confused and have to ask what a column named "Date" is for. Unless the parent object (table) made it unclear because it could easily have two or more date values, in which case a Units suffix is needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply