CODEWITHSUNDEEP
sqlsybase
Gagandeep Singh
Gagandeep Singh

Reputation: 77

Is there a better way to write this SQL query in sybase?

I have a stored procedure with the following query. Based on the @range parameter I am adding more conditions to the where clause. The query below is giving me the correct results. I was wondering if I can avoid repeating the previous "Idx", "IdxType" as the range increases. Is there a better way to write this query?

 SELECT TOP 1 *
    FROM 
        MyTable
    WHERE
    ID1 = @Id1
    and Id1Type = @Id1Type
    and 
    (
        (@range = 2 and ID2 = @Id2 and ID2Type = @Id2Type)
         or
        (@range = 3 and ID2 = @Id2 and ID2Type = @Id2Type 
            and ID3 = @Id3 and ID3Type = @Id3Type)
        or
        (@range = 4 and ID2 = Id2 and ID2Type = @Id2Type 
          and ID3 = @Id3 and ID3Type = @Id3Type
          and ID4 = @Id4 and ID4Type = @Id4Type)
        or
        (@range = 5 and ID2 = @Id2 and ID2Type = @Id2Type 
          and ID3 = @Id3 and ID3Type = @Id3Type
          and ID4 = @Id4 and ID4Type = @Id4Type
           and ID5 = @Id5 and ID5Type = @Id5Type)
    )

Upvotes: 1

Views: 61

Answers (1)

Alexey S. Larionov
Alexey S. Larionov

Reputation: 7927

What you can do is to regroup terms atleast using simple boolean logic

SELECT TOP 1 *
  FROM 
    MyTable
  WHERE
    ID1 = @Id1 and Id1Type = @Id1Type and
    (@range < 2 or ID2 = @Id2 and ID2Type = @Id2Type) and 
    (@range < 3 or ID3 = @Id3 and ID3Type = @Id3Type) and
    (@range < 4 or ID4 = @Id4 and ID4Type = @Id4Type) and
    (@range < 5 or ID5 = @Id5 and ID5Type = @Id5Type)

Upvotes: 2

Related Questions