Usher Usher - 12 days ago 9
SQL Question

Any other way to avoid if else condition

I have a following stored procedure. I have variable

@PercentCleared
.

IF @PercentCleared <= 70
then I have to subtract
PercentCleared-10
else use as it is. Can you please advise me the right way to do?

DECLARE @PercentCleared INT
DECLARE @TenPercent int

SET @TenPercent = 10

IF(@PercentCleared <=70)
BEGIN
SELECT
@I, dbo.tblV.VegTypeCode, dbo.tblV.VegTypeName
FROM
dbo.tblVegetationType
INNER JOIN
dbo.tblVegFormationLink ON dbo.tblV.VegTypeID = dbo.tblVegFormationLink.VegTypeID
WHERE
dbo.tblVegetationType.PercentageCleared >=(@PercentCleared - @TenPercent)
WHERE
a.VegTypeID = dbo.tblVegetationType.VegTypeID
AND dbo.tblVegetationType.VegTypeID <> (SELECT VegTypeID
FROM @EcosystemCredits eco
WHERE eco.theID = @I)
END
ELSE IF
BEGIN
SELECT
@I, dbo.tblV.VegTypeCode, dbo.tblV.VegTypeName
FROM
dbo.tblVegetationType
INNER JOIN
dbo.tblVegFormationLink ON dbo.tblV.VegTypeID = dbo.tblVegFormationLink.VegTypeID
WHERE
dbo.tblVegetationType.PercentageCleared >=@PercentCleared
WHERE
a.VegTypeID = dbo.tblVegetationType.VegTypeID
AND dbo.tblVegetationType.VegTypeID <> (SELECT VegTypeID
FROM @EcosystemCredits eco WHERE eco.theID = @I)

End

Answer

I think you can do this by using a CASE statement in the WHERE clause

SELECT @I,  dbo.tblV.VegTypeCode,  dbo.tblV.VegTypeName FROM dbo.tblVegetationType 
INNER JOIN dbo.tblVegFormationLink
  ON dbo.tblV.VegTypeID = dbo.tblVegFormationLink.VegTypeID
WHERE dbo.tblVegetationType.PercentageCleared 
      >= @PercentCleared 
         - CASE WHEN (@PercentCleared <=70) THEN @TenPercent ELSE 0 END -- change here
WHERE a.VegTypeID = dbo.tblVegetationType.VegTypeID 
AND dbo.tblVegetationType.VegTypeID 
    <> (SELECT VegTypeID FROM @EcosystemCredits eco WHERE eco.theID = @I)