The following are a few suggestions on how I'd write the stored procedure.
If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free & private for teams of 5 or less (this is per project, so 5 developers per project). Then you'll be able to track changes you make to your stored procedures, views, tables, etc.
Use Try, Catch blocks to handle your errors. You can then write them to a log file or table.
Use transactions for rolling back your queries during testing. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be cancelled or rolled back, then all of the data modifications are erased.
Common Table Expressions (CTE)
CTE's in your SQL help with documentation. The expression name can then let other developers know why you used that expression e.g.
If I need to perform more than one DML query on a table, I usually will use a MERGE statement.
I would download the following tool for SSMS and Visual Studio, Poor Man's T-Sql Formatter and on GitHub. I use it when I have to edit other developer's code. It's a great way to standardize your SQL. I find it does most of the formatting for me, but I'll still make a few changes after.
Here are the settings I used:
I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier.
Estimated Execution Plan
It's a good idea to check the Estimated Execution Plan. The shortcut in Microsoft SQL Server Management Studio (SSMS) is Ctrl + L. You can even run 2 queries in the same tab to compare the plans. Then you can see differences in the query plans using STRING_SPLIT.
Without table definitions and sample records I was unable to test this, but it should give you a good start.
ALTER PROCEDURE [dbo].[Admin_Save_PMIDS]
, @PMIDList VARCHAR(MAX)
SET XACT_ABORT ON;
[PMID] = Split.a.value('.', 'VARCHAR(100)')
, [PersonID] = @MemberID
, [MemberID] = @MemberID
SELECT CAST('<M>' + REPLACE(@PMIDList, ',', '</M><M>') + '</M>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/M') AS Split(a)
MERGE [dbo].[PublicationTable] AS T
USING pm_ids AS S
ON T.[PMID] = S.[PMID] AND T.[MemberID] = S.[MemberID] --< Update the join to the correct unique key for the table
WHEN NOT MATCHED BY TARGET
WHEN NOT MATCHED BY SOURCE --< You may want to add a WHERE clause here
--OUTPUT @@SERVERNAME AS [ServerName], DB_NAME() AS [DatabaseName], $action, inserted.*, deleted.* --< show the changed records here
ROLLBACK TRANSACTION; --< use the rollback for testing
--COMMIT TRANSACTION; --< use the commit for production
--you can write this to a log table
[ServerName] = @@SERVERNAME
, [DatabaseName] = DB_NAME()
, [ErrorProcedure] = ERROR_PROCEDURE()
, [ErrorNumber] = ERROR_NUMBER()
, [ErrorSeverity] = ERROR_SEVERITY()
, [ErrorState] = ERROR_STATE()
, [ErrorLine] = ERROR_LINE()
, [ErrorMessage] = ERROR_MESSAGE()
, [ErrorDateTime] = GETDATE();
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
N'The transaction is in an uncommittable state.' + 'Rolling back transaction.';
-- Test whether the transaction is committable.
IF (XACT_STATE()) = 1
N'The transaction is committable.' + 'Committing transaction.';