t-sql: Return 1 row or everything based on optional paramater

Question

I have a stored procedure that takes a single optional parameter. If the parameter exists it should update the 1 record, otherwise everything else. Are there a way to do in with a single SQL statement without using dynamic SQL?

Something like this:

CREATE PROCEDURE UpdateEmployees (@PersonID varchar(10) = null)
  AS
BEGIN 
      UPDATE Employees 
      SET Field1 = 'Changed'
      WHERE (PersonID Is Not Null OR PersonID = ISNULL(@PersonID, '')) --this not 100% yet.
END

Answer 1

You're almost there - you need to perform the update on a row with the supplied @PersonId or if the parameter is null (not if it's not, as you currently have). Additionally, The isnull is redundant, as null will return "unknown" (which is not true) on any = operation.

CREATE PROCEDURE UpdateEmployees (@PersonID varchar(10) = null)
  AS
BEGIN 
      UPDATE Employees 
      SET Field1 = 'Changed'
      WHERE (@PersonID IS NULL OR PersonID = @PersonID)
END