mercredi 9 janvier 2019

Cleaning up 3 nested SQL `IF..ELSE`

I have a table with a large (>50) number of columns. I am writing a stored procedure, where the user wants to pass in (among other parameters) 3 optional parameters to filter the dataset by. Normally my code would look like

IF @value1 IS NULL
   IF @value2 IS NULL
      IF @value3 IS NULL
         WITH...SELECT
      ELSE
         WITH...SELECT
   ELSE
      IF @value3 IS NULL
         WITH...SELECT
      ELSE
         WITH...SELECT
ELSE
   IF @value2 IS NULL
      IF @value3 IS NULL
         WITH...SELECT
      ELSE
         WITH...SELECT
   ELSE
      IF @value3 IS NULL
         WITH...SELECT
      ELSE
         WITH...SELECT

but this seems to cluttered and very complex to follow, especially since WITH is relatively large.

Alternatively,

IF @value1 IS NULL AND
   @value2 IS NULL AND
   @value3 IS NULL
   WITH..SELECT
ELIF @value1 IS NULL AND
   @value2 IS NULL AND
   WITH..SELECT
ELIF ...
   WITH..SELECT
ELIF ...
   WITH..SELECT
...

is more readable but much slower. Is there a cleaner way to do this?

It seems the IFs only affect the WITH part but I cannot put an IF inside the WITH. In addition, the only usage of the fields if not NULL is to constrain via WHERE col1 = @value1 etc'

Is there a cleaner way to do this? Thanks

Aucun commentaire:

Enregistrer un commentaire