CASE must evaluate to a single value.  For this, you need to use "standard" WHERE clause and/ors:

WHERE([Admission Date] IS NOT NULL)
and
(@Parameter = 'A' and [Close...

If I understand correctly, this is what you want:

SELECT
    BCC.FirstName + ' ' + BCC.Lastname AS Matching_Name
FROM (
    SELECT...

A shorter alternative:

SELECT [Col1],
    CASE
        WHEN [Col1] = '0' OR [Col1] = '200' THEN '99991231'
   ...

I'd strongly urge you to make this table driven rather than hard-coding any of this.  If we know anything, it's that taxes always go up!

DROP...

Might be better to just add all the calc'd columns to the underlying table, so that all queries automatically can use those columns:

...
select *
 ...

That format also makes it very convenient to run a SELECT test before the UPDATE, like so:

UPDATE p
SET p.statusid = c.statusid
--SELECT /*TOP...

Does it store the result back into a datetime variable?  If so, using float to strip the time is not a problem.

This topic comes up so much, I think MS should "fudge" a little on the relational model and create a type of "char array".  Then include not just selection against...

A long shot, but SSMS seems to use msdb for a lot of its actions.  I've seen bloated msdbs slow down SSMS considerably, even just expanding the db list.  So...

INSERT @TestTable2
(
    NameRecord,
    SingleName
)
SELECT ca1.*
FROM @TestTable
CROSS APPLY (
    VALUES(ID, NameRecord, Name1), (ID, NameRecord, Name2), (ID, NameRecord,...