3

In the application development there is a concept of defensive programming. How to implement defensive programming techniques and writing robust code using Transact-SQL?

juur
  • 5,633
  • 10
  • 32
  • 36

5 Answers5

5

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
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
2

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
  • 4,367
  • 1
  • 21
  • 18
  • care to give us some details on the _carefully_ aspect of transactions? – rebelliard Sep 22 '10 at 20:16
  • 1
    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 Sep 22 '10 at 20:32
  • 1
    @Rafael Belliard: start late, finish early. never nest. SET XACT_ABORT ON – gbn Sep 22 '10 at 20:36
1
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
  • 2,143
  • 2
  • 19
  • 25
  • 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 Sep 22 '10 at 22:32
1

To consider the psychological angle to your question, you may find DBA Survival Skills – Think Defensively to be interesting reading.

John Sansom
  • 41,005
  • 9
  • 72
  • 84
0

In addition to what all the others said, enforce data integrity in the database!

HLGEM
  • 94,695
  • 15
  • 113
  • 186