CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Usher
Usher

Reputation: 2146

Right way to subtract in a stored procedure

I have the following query in my stored procedure

DECLARE @I INT
DECLARE @TenPercent int
DECLARE @RowsCount int

SET @TenPercent =10
SET @I = 1

SELECT 
    @I, dbo.tblVegetationType.VegTypeCode, dbo.tblVegetationType.VegTypeName
FROM 
    dbo.tblVegetationType 
INNER JOIN 
    dbo.tblVegTypeVegFormationLink ON dbo.tblVegetationType.VegTypeID = dbo.tblVegTypeVegFormationLink.VegTypeID
INNER JOIN 
    tblCMAVegTypeLink ON dbo.tblVegetationType.VegTypeID = dbo.tblCMAVegTypeLink.VegTypeID
WHERE   
    dbo.tblVegetationType.PercentageCleared >= (@PercentCleared - @TenPercent)
    AND dbo.tblVegTypeVegFormationLink.VegetationFormationID = @VegetationFormationID
    AND dbo.tblCMAVegTypeLink.CMAID = @CMAID

I have following condition

dbo.tblVegetationType.PercentageCleared >= (@PercentCleared - @TenPercent)

What I am trying to do here: if PercentCleared is 60% then I want to query to pick the list from 50 %.

So I just add the SET @TenPercent = 10 and subtract from the condition.

Is that a right way to do?

Upvotes: 0

Views: 990

Answers (1)

Deepshikha
Deepshikha

Reputation: 10274

Another way of writing the query can be:

SELECT @I,
       VT.VegTypeCode,
       VT.VegTypeName
FROM dbo.tblVegetationType VT
INNER JOIN dbo.tblVegTypeVegFormationLink VTVF
ON VT.VegTypeID = VTVF.VegTypeID
INNER JOIN tblCMAVegTypeLink CVT
ON VT.VegTypeID = CVT.VegTypeID
WHERE 1= case when  @PercentCleared >= 60 and  VT.PercentageCleared <= 50 then 1
-- you can add condition when @PercentCleared < 60 then what values of   
-- VT.PercentageCleared are to be considered
        when  @PercentCleared < 60 and  VT.PercentageCleared <= 50 then 1
        end
AND VTVF.VegetationFormationID = @VegetationFormationID
AND CVT.CMAID = @CMAID

Upvotes: 1

Related Questions