Generate DELETE Query using ONLY IN operator

Kumar AU

5y997

I am trying to write a query that generates the Delete query automatically based on the requirements. I will get data from different tables and dump into temp table and then using this temp table I need to write query using IN operator, please help me

This is what I have tried

SET
NOCOUNT ON DECLARE @DeleteDisbursement TABLE (
DisbursementId BIGINT,
PolicyNumber NVARCHAR(10),
DisbursementAmount DECIMAL(19, 4)
) DECLARE @DeleteRecords Table(DisbursementId BIGINT, ActivityId BIGINT)
INSERT INTO
@DeleteDisbursement (DisbursementId, PolicyNumber, DisbursementAmount)
VALUES
(4576, '12345', 3.00),
(1232, '65455', 143.44),
(2341, '34234', 228.95),
(1111, '23155', 414.89)
SELECT
*
from
@DeleteDisbursement DECLARE @GenerateScriptCount BIGINT = 0 DECLARE @DisbursementIDValue BIGINT = 0 DECLARE @ActivityIdValue BIGINT = 0 WHILE EXISTS(
SELECT
Top 1 1
FROM
@DeleteDisbursement
) BEGIN DECLARE @DisbursementId BIGINT = 0 DECLARE @PolicyNumber NVARCHAR(10) DECLARE @AccountId BIGINT = 0 DECLARE @ActivityId BIGINT DECLARE @DisbursementAmount DECIMAL(19, 4)
SELECT
TOP 1 @DisbursementId = DisbursementId,
@PolicyNumber = PolicyNumber,
@DisbursementAmount = DisbursementAmount
FROM
@DeleteDisbursement
SET
@ActivityId = (
SELECT
ActivityId
FROM
ActivityLog
WHERE
ActivityAmount = @DisbursementAmount
) IF (@ActivityId > 0) BEGIN
INSERT INTO
@DeleteRecords (DisbursementId, ActivityId)
VALUES(@DisbursementId, @ActivityId)
END
DELETE FROM
@DeleteDisbursement
WHERE
DisbursementId = @DisbursementId
END WHILE EXISTS(
SELECT
Top 1 1
FROM
@DeleteRecords
) BEGIN
SELECT
TOP 1 @DisbursementIDValue = @DisbursementId,
@ActivityIdValue = @ActivityId
FROM
@DeleteRecords PRINT 'DELETE FROM Disbursement Where DisbursementId IN (' + CONVERT(NVARCHAR(MAX), @DisbursementIDValue) + ')' PRINT 'DELETE FROM ActivityLog WHERE ActivityId IN (' + CONVERT(NVARCHAR(MAX), @ActivityIdValue) + ')'
DELETE FROM
@DeleteRecords
WHERE
DisbursementId = @DisbursementId
END
SET
NOCOUNT OFF;

But its not working, Please help me How do I write query only 1 query which included in IN Operator like below :-

DELETE FROM
Disbursement
Where
DisbursementId IN (4576, 1232, 2341, 1111)
DELETE FROM
ActivityLog
WHERE
ActivityId IN (1000, 2000, 3000, 4000)

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kumar AU5y ago
Hi Amith, I am sorry to say that , your new query doesn't work, it will run on infinite loop , can you please help me where it is going wrong
+1
Kumar AU5y ago
Hi Amith, I am sorry to say that , your new query doesn't work, it will run on infinite loop , can you please help me where it is going wrong
+1
Amit Mohanty5y ago
Hey Kumar, please try this
SET NOCOUNT ON;
DECLARE @DeleteDisbursement TABLE (DisbursementId BIGINT,PolicyNumber NVARCHAR(10),DisbursementAmount DECIMAL(19, 4))
DECLARE @DeleteRecords TABLE (DisbursementId BIGINT,ActivityId BIGINT)

INSERT INTO @DeleteDisbursement (DisbursementId, PolicyNumber, DisbursementAmount)
VALUES (4576,'12345',3.00),(1232,'65455',143.44),(2341,'34234',228.95),(1111,'23155',414.89)

DECLARE @GenerateScriptCount BIGINT = 0
DECLARE @DisbursementIDValue NVARCHAR(MAX) = ''
DECLARE @ActivityIdValue NVARCHAR(MAX) = ''
WHILE EXISTS(SELECT Top 1 1 FROM @DeleteDisbursement)
BEGIN
    DECLARE @DisbursementId BIGINT = 0
    DECLARE @PolicyNumber NVARCHAR(10)
    DECLARE @AccountId BIGINT = 0
    DECLARE @ActivityId BIGINT
    DECLARE @DisbursementAmount DECIMAL(19, 4)
    SELECT TOP 1 @DisbursementId=DisbursementId,@PolicyNumber=PolicyNumber,@DisbursementAmount=DisbursementAmount FROM @DeleteDisbursement
    SET @ActivityId=(SELECT ActivityId FROM ActivityLog WHERE ActivityAmount=@DisbursementAmount)
    IF (@ActivityId > 0) BEGIN INSERT INTO @DeleteRecords (DisbursementId,ActivityId) VALUES(@DisbursementId,@ActivityId) END
    DELETE FROM @DeleteDisbursement WHERE DisbursementId = @DisbursementId
END

WHILE EXISTS(SELECT Top 1 1 FROM @DeleteRecords)
BEGIN
    SELECT TOP 1 @DisbursementID = DisbursementId, @ActivityId = ActivityId FROM @DeleteRecords
    IF (@DisbursementIDValue = '') BEGIN SET @DisbursementIDValue =  CONVERT(NVARCHAR(MAX), @DisbursementID) END
    ELSE BEGIN SET @DisbursementIDValue =  @DisbursementIDValue + ',' + CONVERT(NVARCHAR(MAX), @DisbursementID) END

    IF (@ActivityIdValue = '') BEGIN SET @ActivityIdValue =  CONVERT(NVARCHAR(MAX), @ActivityId) END
    ELSE BEGIN SET @ActivityIdValue =  @ActivityIdValue + ',' + CONVERT(NVARCHAR(MAX), @ActivityId) END
    DELETE FROM @DeleteRecords WHERE DisbursementId = @DisbursementID
END

IF @DisbursementIDValue <> '' BEGIN
DECLARE @QRY1 NVARCHAR(MAX) = 'DELETE FROM Disbursement Where DisbursementId IN (' + @DisbursementIDValue + ')'
EXEC (@QRY1) END
IF @ActivityIdValue <> '' BEGIN
DECLARE @QRY2 NVARCHAR(MAX) = 'DELETE FROM ActivityLog WHERE ActivityId IN (' + @ActivityIdValue + ')'
EXEC (@QRY2) END
SET NOCOUNT OFF;
-1
Kumar AU5y ago
Thank you Amit, for your time and response. When I run your modified query, its giving me an error message -
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

It seems to be below lines are giving an error message,
DECLARE @QRY1 NVARCHAR(MAX) = 'DELETE FROM DC_BIL_Disbursement Where DisbursementId IN (' + @DisbursementIDValue + ')'
EXEC (@QRY1)
DECLARE @QRY2 NVARCHAR(MAX) = 'DELETE FROM DC_BIL_ActivityLog WHERE ActivityId IN (' + @ActivityIdValue + ')'
EXEC (@QRY2)

Kindly help me
+1
Amit Mohanty5y ago
Hey, try the below query.
SET NOCOUNT ON
DECLARE @DeleteDisbursement TABLE (DisbursementId BIGINT,PolicyNumber NVARCHAR(10),DisbursementAmount DECIMAL(19, 4))
DECLARE @DeleteRecords TABLE (DisbursementId BIGINT,ActivityId BIGINT)

INSERT INTO @DeleteDisbursement (DisbursementId, PolicyNumber, DisbursementAmount)
VALUES (4576,'12345',3.00),(1232,'65455',143.44),(2341,'34234',228.95),(1111,'23155',414.89)

DECLARE @GenerateScriptCount BIGINT = 0
DECLARE @DisbursementIDValue NVARCHAR(MAX) = ''
DECLARE @ActivityIdValue NVARCHAR(MAX) = ''
WHILE EXISTS(SELECT Top 1 1 FROM @DeleteDisbursement)
BEGIN
    DECLARE @DisbursementId BIGINT = 0
    DECLARE @PolicyNumber NVARCHAR(10)
    DECLARE @AccountId BIGINT = 0
    DECLARE @ActivityId BIGINT
    DECLARE @DisbursementAmount DECIMAL(19, 4)
    SELECT TOP 1 @DisbursementId=DisbursementId,@PolicyNumber=PolicyNumber,@DisbursementAmount=DisbursementAmount FROM @DeleteDisbursement
    SET @ActivityId=(SELECT ActivityId FROM ActivityLog WHERE ActivityAmount=@DisbursementAmount)
    IF (@ActivityId > 0) BEGIN INSERT INTO @DeleteRecords (DisbursementId,ActivityId) VALUES(@DisbursementId,@ActivityId) END
    DELETE FROM @DeleteDisbursement WHERE DisbursementId = @DisbursementId
END

WHILE EXISTS(SELECT Top 1 1 FROM @DeleteRecords)
BEGIN
    SELECT TOP 1 @DisbursementID = DisbursementId, @ActivityId = ActivityId FROM @DeleteRecords
    IF (@DisbursementIDValue = '') BEGIN SET @DisbursementIDValue =  CONVERT(NVARCHAR(MAX), @DisbursementID) END
    ELSE BEGIN SET @DisbursementIDValue =  @DisbursementIDValue + ',' + CONVERT(NVARCHAR(MAX), @DisbursementID) END

    IF (@ActivityIdValue = '') BEGIN SET @ActivityIdValue =  CONVERT(NVARCHAR(MAX), @ActivityId) END
    ELSE BEGIN SET @ActivityIdValue =  @ActivityIdValue + ',' + CONVERT(NVARCHAR(MAX), @ActivityId) END
    DELETE FROM @DeleteRecords WHERE DisbursementId = @DisbursementID
END

DECLARE @QRY1 NVARCHAR(MAX) = 'DELETE FROM Disbursement Where DisbursementId IN (' + @DisbursementIDValue + ')'
EXEC (@QRY1)
DECLARE @QRY2 NVARCHAR(MAX) = 'DELETE FROM ActivityLog WHERE ActivityId IN (' + @ActivityIdValue + ')'
EXEC (@QRY2)
SET NOCOUNT OFF;
0