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