In the application development there is a concept of defensive programming. How to implement defensive programming techniques and writing robust code using Transact-SQL?
+1
A:
IF EXISTS()
is something that should be used a lot more often in T-SQL I think. A lot of times when developers write SQL code they don't think in terms of exceptions and faults as they do when writing regular code.
Scott
2010-09-22 20:04:45
Don't be afraid of RAISEERROR either if you are given invalid input for instance. This can be used like a ArgumentException in managed code to bubble issues back to the caller rather than just breaking or returning corrupt results.
TheCodeKing
2010-09-22 22:32:01
+3
A:
- Simple Talk...
- ...books
- SQL Server Central
- Alexander Kuznetsov (who is SO user AlexKuznetsov)
More generally
- Understand TRY..CATCH and error handling
- Datatype safety (no number compare against nvarchar for example)
- Understand transactions
- Consider stored procedures
- Understand SQL injection
gbn
2010-09-22 20:06:39
+2
A:
To add to what Scott said:
- Use TRY / CATCH which is now supported in SQL Server
- Validate the parameters of your procedures and use RAISERROR when things don't pass
- Use transactions (carefully)
Mike Forman
2010-09-22 20:12:10
care to give us some details on the _carefully_ aspect of transactions?
Rafael Belliard
2010-09-22 20:16:51
By carefully, I mean don't simply wrap an entire procedure in begin transaction ... commit transaction. Think about what needs to be transactional handle it accordingly
Mike Forman
2010-09-22 20:32:23
+1
A:
To consider the psychological angle to your question, you may find DBA Survival Skills – Think Defensively to be interesting reading.
John Sansom
2010-09-22 20:30:29
A:
In addition to what all the others said, enforce data integrity in the database!
HLGEM
2010-09-23 21:05:04