There are two important ways to improve the readability and maintainability of T-SQL code.

The first method is to write "pretty code" -- by that, I mean to format the code in a logical way, that helps to explain your logic, not obscure it. Here is an example of actual production code from a commercially-available application:


CREATE TRIGGER PERSON_UPDATE_DATETIME ON dbo.Person
FOR INSERT, UPDATE
AS
UPDATE [dbo].[Person]
SET UpdatedDateTime = GETDATE()
FROM inserted i
WHERE i.personid = [dbo].[Person].personid
Update [dbo].[Person]
Set Nrdsmember_id =
(Case When nrdsmember_id = 0
then NUll Else nrdsmember_id End)
where nrdsmember_id = 0
update Person
Set LoginID =
[dbo].[Person].lastname, Password =
[dbo].[Person].nrdsmember_id
where (len(password) <>



Re-formatting to help reveal its purpose:


UPDATE
  [dbo].[Person]
SET
  UpdatedDateTime = GETDATE()
FROM
  inserted i
WHERE
  i.personid = [dbo].[Person].personid

UPDATE
  [dbo].[Person]
SET
  Nrdsmember_id =
    CASE
      WHEN nrdsmember_id = 0
      THEN NUll
      ELSE nrdsmember_id
      END
WHERE
  nrdsmember_id = 0

UPDATE
  Person
SET
  LoginID = [dbo].[Person].lastname,
  Password = [dbo].[Person].nrdsmember_id
WHERE
  (LEN(password) < 1 AND LEN(loginid) < 1)
  OR
  (password IS NULL AND loginid IS NULL)

 


We can now clearly see three separate SQL statements. Now on to the second method of code improvement - use the best command for the task. Examine the following:



UPDATE
  [dbo].[Person]
SET
  Nrdsmember_id =
    CASE
      WHEN nrdsmember_id = 0
      THEN NUll
      ELSE nrdsmember_id
      END
WHERE
  nrdsmember_id = 0



If you study what it does, it is supposed to prevent a certain field from being set to zero, which in the business context is not valid, therefore we need the field set to NULL instead.

What improvements can be made here? First of all, there is a built-in function NULLIF which compares two expressions, and returns NULL if equal; otherwise it returns the first expression. So we might use:

Nrdsmember_id = NULLIF(nrdsmember_id, 0)

...which eliminates the CASE function. But look - the WHERE clause is limiting us to only those records where the field = 0 so we're doing double work. We might just use:

UPDATE [dbo].[Person] SET Nrdsmember_id = NULLIF(nrdsmember_id, 0)
...since we're only going to change records where the field = 0...

...or we could use this:

UPDATE [dbo].[Person] SET Nrdsmember_id = NULL
WHERE Nrdsmember_id = 0
...which is about as simple as you can get.

But wait there's more...this is inside a TRIGGER which means that we're going to touch many more records than we need to. Unless we join to the inserted table in this final version:



UPDATE
  p
SET
  p.Nrdsmember_id = NULL
FROM
  Person p
  INNER JOIN inserted i
    ON i.PersonID = p.PersonID
WHERE
  p.Nrdsmember_id = 0