Hi,
I'm using SQL Server 2019, I need to create a stored procedure, which must iterate on multiples rows.
Here's what I've done so far, based on information I found on the internet :
USE [Metzenger]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DeleteUserAccount] @ID_account INT
AS
DECLARE @ID INT;
SELECT @ID = message_id FROM Metzenger.dbo.Message WHERE account_id = @ID_account;
WHILE @ID is not null
BEGIN
EXEC dbo.DeleteUserMessages @ID;
END
DELETE FROM Metzenger.dbo.Message
WHERE account_id = @ID_account;
DELETE FROM Metzenger.dbo.Invite
WHERE account_id = @ID_account;
DELETE FROM Metzenger.dbo.Access
WHERE account_id = @ID_account;
DELETE FROM Metzenger.dbo.UserAccount
WHERE account_id = @ID_account;
Unfortunately the result is an infinite loop that always processes the same message id.
This is what the nested stored procedure contains :
USE [Metzenger]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DeleteUserMessages] @ID INT
AS
PRINT @ID;
DELETE FROM Metzenger.dbo.AttachedFile
WHERE message_id = @ID;
DELETE FROM Metzenger.dbo.Archive
WHERE message_id = @ID;
DELETE FROM Metzenger.dbo.Consult
WHERE message_id = @ID;
Any help would be greatly appreciated.
Thank you.