Sql – What’s the shortest TSQL to concatenate a person’s name which may contain nulls

Some thoughts:

• If you have one trigger each for first, middle and last, then I guess you have 3 triggers, 3 scans of the "inserted" table, and 3 updates

• Your current code does not support multiple row updates or inserts

• By using "...where [First] = @ChangedField" then if you'll probably be updating multiple rows. Let's say you entered "John" or "Hans". Although John or Hans is correct, you're still updating every row with John or Hans. And ignoring other rows inserted (see previous point)

Options:

• Combine into one trigger
• Update only changed rows
• Add a filter for old/new column differences
• Reduce work (eg combine one statement for all columns)

Example of what you could do:

ALTER TRIGGER [dbo].[CopyNames]
   ON  [dbo].[Patients]
   AFTER  INSERT,UPDATE
AS 
BEGIN
SET NOCOUNT ON;

IF NOT EXISTS (SELECT * FROM INSERTED)
    RETURN;

IF UPDATE(First) OR UPDATE(Middle) OR UPDATE (Last)
  update
      P
  set
      FirstName  = INS.First,
      Middlename  = INS.Middle,
      LastName  = INS.Last
  FROM
      dbo.Patients P
      JOIN
      INSERTED INS ON P.KeyColumn = INS.KeyColumn
/* optional, try it and see
  WHERE
      P.FirstName <> INS.First OR
      P.Middlename <> INS.Middle OR
      P.LastName <> INS.Last
*/


END

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

Add OPTION (RECOMPILE) onto your query, see Erland's article, and SQL Server will resolve the OR from within (@LastName IS NULL OR LastName= @LastName) before the query plan is created based on the runtime values of the local variables, and an index can be used.

This will work for any SQL Server version (return proper results), but only include the OPTION(RECOMPILE) if you are on SQL 2008 SP1 CU5 (10.0.2746) and later. The OPTION(RECOMPILE) will recompile your query, only the verison listed will recompile it based on the current run time values of the local variables, which will give you the best performance. If not on that version of SQL Server 2008, just leave that line off.

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))
        OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
    END