0

Hi I want to delete several hundred records at once, but the system demands I use a stored procedure. I'm wondering if I can execute the procedure and do something like the following?

BEGIN TRANSACTION 

    EXECUTE UserDelete @ID = SELECT ID FROM #idlist, -- bigint
                       @ModifiedByLoginID = 11111 -- bigint

ROLLBACK TRANSACTION
--COMMIT TRANSACTION

I have tried this and it does not work so I'm just wondering if theres something I'm missing? Something else I need to add? Is this possible at all?

If more information is required please ask. Any help would be great.

User AlexK. (first comment below) pointed me in the direction of this link. SQL Call Stored Procedure for each Row without using a cursor It was exactly the answer I needed. I can't mark his comment as the answer so this edit seems appropriate.

JimmyPop13
  • 307
  • 1
  • 13
  • 2
    As in [SQL Call Stored Procedure for each Row without using a cursor](https://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor) ? – Alex K. Oct 02 '18 at 10:20
  • That looks like it could work for me, will give it a go :) – JimmyPop13 Oct 02 '18 at 10:22
  • 2
    "_the system demands I use a stored procedure_"... what system? And what do you mean by "_it doesn't work_"? You need to provide more information. My guess is that you can't simply delete users, as there will be other data related to the user table that will prevent you deleting them. – Tanner Oct 02 '18 at 10:22
  • @AlexK. Thanks for pointing me in the right direction, that was exactly what I needed. I have adapted it to my needs and it works perfectly. – JimmyPop13 Oct 02 '18 at 10:31
  • You need to loop through all values in your `#idlist` table – bastos.sergio Oct 02 '18 at 11:00

0 Answers0