Sample Header Ad - 728x90

Delete all rows if input parameter is 0 otherwise delete only those entries which match the input param

0 votes
2 answers
289 views
I have a stored proc which is receiving id as input. If the id which is received is 0 then we need to delete all the rows otherwise we need to delete only those rows which matches the input id. I can do this by splitting my query using an if-else but that's the catch here, we want to do it in a single query by using JOINS and CASE (may be). Here is my table. CREATE TABLE InfineonFiles ( infimax INTEGER NOT NULL, neonId INTEGER NOT NULL, eTime INTEGER NOT NULL, pTime INTEGER NOT NULL, cisId INTEGER NOT NULL ) Input attribute is say, DECLARE @inpId INT = [ 0 OR POSITIVE INTEGER] Deletion is like this IF @inpId = 0 DELETE TOP (5000) AIA FROM InfineonFiles WHERE pTime < @timeUnitInSeconds ELSE DELETE TOP (5000) AIA FROM InfineonFiles WHERE pTime < @timeUnitInSeconds AND cisId = inpId The above needs to be in a single query. How can I do that?
Asked by Himanshuman (197 rep)
Aug 11, 2023, 09:46 AM
Last activity: Aug 11, 2023, 11:40 AM