SQL Server 2014 – DELETE with OUTPUT

You can confirm whether a given value contains a capital letter or special character with a function. Using a function will allow you to fully customize exactly which characters you want to reject/accept.

Then just use the CheckPassword function in your stored proc, and it will return a BIT/INT value that indicates whether the password is valid or not.

Or if you want to keep it in the procedure, just use REGEX, something like:

WHERE @PASSWORD COLLATE sql_latin1_general_cp1_cs_as NOT LIKE '%[ABCDEFGHIJKLMNOPQRZTUVWXYZ]%'
AND @PASSWORD NOT LIKE '%[!@#$%^&*();[[]],./]%'

The COLLATE is necessary to make the value Case Sensitive, which is important in your case.

You should add the above either as an initial check, or as a new function which you call within the procedure. This check/function will essentially not allow the rest of the procedure to run if it finds that the password does not meet the criteria.

There is no WHERE in that part of the MERGE statement. See MERGE (Transact-SQL) in the documentation for syntax help.

There is an optional AND part in WHEN MATCHED clause so the straightforward answer is to move the condition there:

MERGE @Emp emp
USING @EmpUpdates eup
ON emp.empid = eup.empid
WHEN MATCHED 
     AND emp.empaddress <> eup.empaddress
  THEN
    UPDATE
    SET emp.empaddress = eup.empaddress
WHEN NOT MATCHED BY TARGET 
  THEN 
    INSERT (empid, empaddress)
    VALUES (eup.empid, eup.empaddress) ;

Be careful to handle nulls correctly there (if empaddress is nullable).

It is sometimes more explicit (and efficient) to perform the operations in two separate statements (UPDATE and INSERT in your case) in a transaction.

Also, be aware there have been (and still are) some issues with MERGE.