I have a table that contains a list of stock; user input comes from a parameter named
@StockID. An important condition of the query is only showing stock with a quantity greater than 0.
SELECT Stock.ID, Stock.Description, Stock.Quantity FROM Stock WHERE Stock.Category IN ('X', 'Y') AND Stock.Quantity > 0 AND Stock.ID LIKE @StockID
If there are results, I need to return the records from the query above. When the query returns no results, I need to be able to tell the end-user either:
- The stock item is currently 'out of stock'.
- The stock ID entered is invalid.
Any stock outside the categories defined in the
WHERE clause should be considered as non-existent.
How would I go about efficiently solving this problem? I know that I can use
IF EXISTS on the query where I omit
Stock.Quantity > 0 but I fear that using
IF statements will lead to lots of duplicate code.