yes you will need to either change the code and have multiple conditions or use dynamic sql to build the output if your final output should have each "string" placed...

PARSENAME won't work for more than 4 "parts".

and instead of trying to invent the wheel again look at already made functions

https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2 -- use this one as it is faster than...

unless changed the deploy process and/or schema compare will disable all triggers (both DML and database) - make sure you change that setting.

I would say that your left outer join to PersuKetsi results in you having more rows being processed than on the query you get the count(*) from.

change your first query...

are you sure its the designer? I've never seen it to make that.

but the script generation is another story - the defaults remove lots of things, and this is a...

Thanks for that.   We use synonyms to refer to other databases so hopefully that shouldn't cause too many issues.

I'll give it a go with a test database...

Thanks for that.   We use synonyms to refer to other databases so hopefully that shouldn't cause too many issues.

I'll give it a go with a test database and see...

create an extended event session and log all the code being issued by the pc where you execute that app - that will give you full list of sql statements...

search for the content instead of the object id

SELECT * 
FROM sys.sql_modules
WHERE definition like '%usp_drop_temp_ingenix_codes%' 
and see what you get.

I would also recreate the SP just in...

most of the code looks like someone used to Oracle that was moved to work with SQL Server - in Oracle world majority of things are case sensitive - and...

check if someone had DENY'ed create tables in TEMPDB- or even deny'ed other permissions that could cause the issue.

It is the Portuguese for AI

how many rows of tbl A (18900000  total) have the ReferenceNum = 0 ?

and why aren't you filtering them when doing the insert into the temp table?

I also see, dev team is using DISTINCT operator as a common practice. Is there any proper ways to re-write to the queries to avoid the usage of distinct...

You said in your narrative in your original post that "it reads last 30 mins of data from the tables involved" and yet ALL of your...