How to set multiple values in a variable and use it in multiple DML statements in SQL Server?

Question

I was writing an SQL query to delete multiple entries from multiple tables and databases. I have tried this query for single entry and it worked. But when I was trying to pass multiple string in single go it gave me an error. Here was my code:

Declare @etableId varchar(200) ='STRING_111';

Use DB1
Begin
delete from EPId where PId=@etableId 
delete from CCT where PId =@etableId 
delete from Template where id=@etableId
End
 
Use DB2
Begin
Declare @GSID int;
Select @GSID = id from GS where EId=@etableId;
print @GSID
delete from Ticket where GSI = @GSID
delete from Subset where GSI = @GSID
delete GS where id = @GSID
delete from TP where PTID= @etableId
delete from TP where PTID = @etableId
delete from Temp where id = @etableId
End

I was trying to declare multiple string in the query like: "Declare @etableId varchar(200) =('STRING_111','STRING_222');". On executing this query, I am getting a Syntax error near , error. It does not accept multiple string value by this approach.

Answer 1

I have tried 'Table' variable to accept multiple values and it worked for me. Here is the code:

Declare @etableId TABLE
(
Value varchar(1000)
)

insert into @etableId values ('STRING_111')
insert into @etableId values ('STRING_222')
insert into @etableId values ('STRING_333')
    
Use DB1
Begin
delete from EPId where PId IN (SELECT VALUE FROM @etableId)
delete from CCT where PId IN (SELECT VALUE FROM @etableId)
delete from Template where id IN (SELECT VALUE FROM @etableId)
End
 
Use DB2
Begin
DECLARE @GSIDS TABLE (ID INT); 
INSERT into @GSIDS (ID) SELECT id FROM GS WHERE EId IN (SELECT VALUE FROM @etableId);    
delete from Ticket where GSI IN (SELECT ID FROM @GSIDS)
delete from Subset where GSI IN (SELECT ID FROM @GSIDS)
delete GS where id IN (SELECT ID FROM @GSIDS)
delete from TP where PTID IN (SELECT VALUE FROM @etableId)
delete from TP where PTID IN (SELECT VALUE FROM @etableId)
delete from Temp where id IN (SELECT VALUE FROM @etableId)
End

The above code accepts multiple values in a variable, which we can use in multiple DML statements.

Thanks.!