November 14, 2011 at 6:09 am
Dev (11/14/2011)
It's not a run-time error, try-catch can't catch it.
Well, it should.
No it shouldn't.
p.s. A runtime error does not cause a crash unless the person who wrote the code was incompetent and didn't bother with any error handling.
Agree but the assumption is Developer didn't do anything extra except writing the logic for the application.
Then the developer is incompetent and should find another job because they have no business developing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 6:34 am
well thanks for all of your replies but i have just started using sql so am a little rusty on the syntax ect.
November 14, 2011 at 6:47 am
martin.kerr 34088 (11/14/2011)
well thanks for all of your replies but i have just started using sql so am a little rusty on the syntax ect.
No worries.
The syntax for update catches a lot of people because it's not all that intuitive, especially the more complex updates
It's UPDATE ... SET ... WHERE if you're only referring to one table, or UPDATE ... SET ... FROM ... WHERE if you need multiple tables (like updating one table with values from another)
There are quite a few examples here: http://msdn.microsoft.com/en-us/library/ms177523.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 7:18 am
GilaMonster (11/14/2011)
Dev (11/14/2011)
It's not a run-time error, try-catch can't catch it.
Well, it should.
No it shouldn't.
Actually, it can, although in an indirect way:
CREATE PROCEDURE dbo.Test1 AS
BEGIN TRY
SELECT * FROM NonExistingTable
END TRY
BEGIN CATCH
PRINT 'Error caught in Test1'
END CATCH
;
GO
CREATE PROCEDURE dbo.Test2 AS
BEGIN TRY
EXECUTE dbo.Test1
END TRY
BEGIN CATCH
PRINT 'Error caught in Test2'
END CATCH
;
GO
EXECUTE dbo.Test1
--Result:
-- Msg 208, Level 16, State 1, Procedure Test1, Line 3
-- Invalid object name 'NonExistingTable'.
EXECUTE dbo.Test2
--Result:
-- Error caught in Test2
From BOL:
You can use TRY…CATCH to handle errors that occur during compilation or statement-level recompilation by executing the error-generating code in a separate batch within the TRY block. For example, you do this by placing the code in a stored procedure or by executing a dynamic Transact-SQL statement using sp_executesql. This allows TRY…CATCH to catch the error at a higher level of execution than the error occurrence.
November 14, 2011 at 9:05 am
Dev (11/14/2011)
but that is not standard SQL, support for that construct may be removed in future (not any time soon, though)
I was unaware of this fact. Are you sure on it?
It still appears in UPDATE (Transact-SQL) syntax for SQL Server "Denali", without any side note / warning.
http://msdn.microsoft.com/en-us/library/ms177523(v=SQL.110).aspx
Yes, I'm sure it's not standard sql.
Yes, I'm sure it will not become unsupported any time soon.
Yes, as it's unsupported MS may decide to deprecate it and eventually remove support - there is a risk that will happen. But I'm very sure that this won't happen any time soon. My reaoning is this: Now that MERGE is available in T-SQL, this particular MS non-standard extension is no longer needed. But without merge it was absolutely essential for writing efficient code, so there will be tons of old code out there that would be disrupted if the feature were withdrawn, and MS is not interested in pissing off hordes of customers (especially customers running MS software on big servers, which is a market where it is very weak compared to its position on the corporate desktop). So it will not happen until MS reckons that most such legacy code will have been moved forwards to use the standard feature instead.
And of course at least one extremely well-known MVP has already expressed his views that deprecating it before it is 100% clear that the new standard features have been proved to do at least as well would be criminally stupid.
Tom
November 14, 2011 at 9:24 am
GilaMonster (11/14/2011)
Ok, let's put it this way.What kind of error is this?
SELECT Col1, Col2, Col3 FROM TableThatDoesNotExistIt's not a syntax error, that passes syntax check successfully.
It's not a run-time error, try-catch can't catch it.
p.s. A runtime error does not cause a crash unless the person who wrote the code was incompetent and didn't bother with any error handling.
Well, I'm going to call it a binding error (which won't surprise you at all) because that's what we called similar errors in 1967 when I first worked on language design and compilation, and I can't see any reason to change the terminology for T-SQL just because it has a JiT compiler instead of generating the execution instructions on punched paper tape to be used some time later. :-P:-D
But the cause of this binding error is that the syntax used did not allow the compiler to bind this name, so Dev may have a point in suggesting that it ought to be detected as a syntax error. But I'm not going to hare off and raise a connect item on it, because I still think of it as a binding error, although if someone else raised such a connect item and provided a really convincing justification I might vote for it. (Ouch! the top of this fence is too sharp! :hehe: :Whistling:)
p.s. Your p.s. indicates how much we have moved forward since 1967, when most languages didn't prermit us to write any error handling at all. Or maybe how much back we've moved since the good old days when we checked for error conditions ourselves so that we could do proper containment, reporting, repair, and recovery 😉 instead of letting them cause an exception.
Tom
November 14, 2011 at 9:56 am
There was an interesting discussion on Connect following Hugo's suggestion that the UPDATE...FROM syntax be deprecated:
http://connect.microsoft.com/SQLServer/feedback/details/332437/deprecate-update-from-and-delete-from
14 up-votes and 52 down-votes as of now. I tend to align more with Erland's position than Hugo's, but there are certainly good points on both sides.
Separately, I would say that MERGE is a great idea, but some details of the implementation are less than awesome right now, and there have been far too many bugs involving MERGE (and foreign key checking in particular) for my liking. Especially when ultimate performance is required, I often find myself manually writing an outer/full join plan rather than using MERGE. Horses for courses though - like anything else in SQL Server it's important to know the strengths and weaknesses of each option and choose appropriately.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 14, 2011 at 10:08 am
Peter Brinkhaus (11/14/2011)
GilaMonster (11/14/2011)
Dev (11/14/2011)
It's not a run-time error, try-catch can't catch it.
Well, it should.
No it shouldn't.
Actually, it can, although in an indirect way:
Error handling in T-SQL is a bit of a mess, overall. Some of the changes in SQL Server 2012 (like THROW) seem to indicate that there is a long-term goal to make things a bit more consistent, but that's going to take a long time I guess. The current situation with obscure rules for statement-, scope-, batch- and connection-aborting errors is partly a backward-compatibility thing, but even so, it can be very frustrating.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 14, 2011 at 10:15 am
SQL Kiwi (11/14/2011)
Error handling in T-SQL is a bit of a mess, overall.
That's the understatement of the month. My current pet favourite - non-fatal errors (ie not sev 20+) that automatically roll back any open transaction without terminating the connection.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply