I agree with Joe on the best approach, but if you have no control over the table you will need to use the substring, left and right functions of sql...

Just a guess here, but on the database there is an option called "Nested Triggers Enabled". In your situation I believe this should be set to true. Please verify the...

Kind of an odd query, but this might help get you closer.

DECLARE @PatientIDToMatch int = 1
SELECT a.Patients_ID, Count(*)
FROM dbo.PatientTreatment a
...

Are you able to retrieve the result with the following? It works for me on SQL2016

Select Replace ('abc[def', '[','')
will return
abcdef

If you are already using Oracle for reporting purposes, then I would tend to keep the tables in Oracle and provide access to those table for the .net app instead...

It sounds like you need to rethink (as you are doing) the scalability of these applications/databases.
If I understand you correctly, you need additional tables from Oracle for reporting purposes....

Also, I noticed that your requirement was only to check if the category_id was unique.
In your query, you are checking if category_id and category_name together are unique. (Not sure...

Some best practice that I use and should help you understand your code better are as follows:

1) Be sure to indent your code blocks
2) I use verbose...

the only thing I can see is that you do not have the BEGIN/END block for the creation of the procedure: Compare my code below to yours.

[code...

If I understand your requirements correctly, this should do the job: Notice that I added the where condition to the delete
CREATE PROCEDURE delete_asset_log 
@assetlog_id INT
AS
BEGIN
IF EXISTS...