I say don't use IIF.  It's a completely foreign coding approach to other SQL.  And there's no genuine need for it, since CASE can do the same thing, and IIF...

If you want to rely on the existing zip always being fully padded on the left with zeros, you can do this:

CASE WHEN LEN(D.Zip) = 5 AND...

You could probably use TRIGGER_NESTLEVEL to do that.

I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>').  If you want more info on this...

Don't use functions in a WHERE clause, that's a very bad habit.  Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some...

'19 Nov 2022' is converts to the datetime value of '19 Nov 2022 00:00:00.000', which is earlier (less than) '19 Nov 2022 15:19:12.000'.

Try:

WHERE [...]
AND CONVERT(date, TransferredDate)...

And the view did not exist before the proc was called and still did not exist after it was called?

If so, put the SQL in a variable and print it...

The best way is to use > the next day rather than <= the end day.  Like this:

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '18 Nov 2021'
SET...

I would think it would be.  Why not try it with a small table and see?

SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate FROM History WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO)) ORDER BY EffDate ASC

How large (MB/GB) is the "History" table?

How is...

Keep in mind that the view is being created in the same db as the proc is in.  Although presumably that is what you want to do.

What does "doesn't work"...

I'd clean up the prod problem first.  You can use a backup and research to research the other stuff, if you think it's necessary.

Also, once you get the table reduced...

Don't rely on the DB_ID() to reference a db.  Assume it could change at any time.

If you'll notice, in its system tables, MS stores the db name rather than the...

As Jeff noted, you should be able to do this with dynamic SQL.

But be aware that you cannot have any GO statements in the code.  Just the main statement itself:

CREATE...

SELECT C1
FROM #Base
WHERE C2 IN ('122', '123')
GROUP BY C1
HAVING SUM(CASE WHEN C2 = '123' THEN 1 ELSE 0 END) > SUM(CASE WHEN C2 = '122' THEN 1...

Jeff,

You're right this is not ok. Just this morning I alse remembered myself, I have some scenes implemented, which are turning off particular lights even if the light are...