SQLTeam.com | Weblogs | Forums

How to iterate on a SELECT statement?

Transact-SQL
#1

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.

#2

This will always return one row so the loop is unnecessary as the id never changes Nd hence stuck in loop

#3 

USE [Metzenger]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[DeleteUserAccount] 
    @ID_account INT
AS

SET NOCOUNT ON;
DECLARE @ID INT;
SET @ID = 0

WHILE @ID is not null
BEGIN

EXEC dbo.DeleteUserMessages @ID;

DELETE FROM dbo.Message
WHERE account_id = @ID_account;

DELETE FROM dbo.Invite
WHERE account_id = @ID_account;

DELETE FROM dbo.Access
WHERE account_id = @ID_account;

DELETE FROM dbo.UserAccount
WHERE account_id = @ID_account;

SELECT @ID = message_id FROM dbo.Message 
WHERE account_id = @ID_account;

END /*WHILE*/
/*end of proc*/
GO
#4

Thank you Scott, but I get an infinite loop, the message_id (0) not updating.

#5

Thank you yosiasz, I don't understand why it will always return one row whereas it must return all the messages bound to the targeted user.

#6 

USE [Metzenger]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[DeleteUserAccount] 
    @ID_account INT
AS

SET NOCOUNT ON;
DECLARE @ID INT;
SET @ID = 0

WHILE @ID is not null
BEGIN

EXEC dbo.DeleteUserMessages @ID;

DELETE FROM dbo.Message
WHERE account_id = @ID_account;

DELETE FROM dbo.Invite
WHERE account_id = @ID_account;

DELETE FROM dbo.Access
WHERE account_id = @ID_account;

DELETE FROM dbo.UserAccount
WHERE account_id = @ID_account;

SET @ID = NULL
SELECT @ID = message_id FROM dbo.Message 
WHERE account_id = @ID_account;

END /*WHILE*/
/*end of proc*/
GO
#7

Great ! It works this time.
Thanks a lot.