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
Last activity: Aug 11, 2023, 11:40 AM