mercredi 22 février 2017

How to use IF statement inside WITH?

Wasn't able to find a proper example of this (or maybe I am simply looking in wrong direction).

Depending on incoming parameter value I need to change WHERE condition for SELECT.

For example, I got parameter @bookType with value 'All' and I need to make something like this:

IF @bookType = 'All'
  SELECT * FROM tBooks
  WHERE BookType != 'Template' AND BookGroup='Library'
ELSE
  SELECT * FROM tBooks
  WHERE BookType = @bookType AND BookStatus=@bookStatus

Seems simple, but I need this IF inside WITH:

WITH Books AS (
  IF...
  ...
), bookIds AS (
  ...
  ...
)

And this is not working because I am starting to receive 'Incorrect syntax near IF'. What exactly I am doing wrong? Or maybe it is possible to hide this IF inside WHERE statement (change where depending on parameter value)?

Aucun commentaire:

Enregistrer un commentaire