-1

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.

1

The solution to this involves both database and application. Remove the quantity check from the query so it looks like this

SELECT
    Stock.ID,
    Stock.Description,
    Stock.Quantity
FROM Stock
WHERE Stock.Category IN ('X', 'Y')
    AND Stock.ID LIKE @StockID

In the application check the resultset. If it is empty the StockID is invalid. If there are rows the StockID is valid. Do not copy to the UI any row where the quantity is zero.

  • I agree with this implementation. However, by omitting Stock.Quantity > 0 I will return at least an additional 15,000 records in the query (records with 0 stock) before the list is filtered down. Is this an acceptable overhead for the method of implementation? – dkmann Aug 2 '16 at 10:34
  • Wow. 15,000 - that's a lot. Only you have the information to say if it is "acceptable" but I'm pretty sure that will not scale well. Can these un-displayable rows be removed from the table? Is there a separate Product table that StockID can be checked against? Could the query be structured as a CTE so the parts of the code that are common can be written only once and referenced by EXISTS and data-returning part (don't have a test instance handy just now). – Michael Green Aug 2 '16 at 11:29
  • Thanks for the input Michael. I've decided to go with structuring as a CTE as that seems like the best route to take for now. – dkmann Aug 4 '16 at 10:10
0

Since Your are not ok with Exists ..I suggest below Process..

1.Create one more stored procedure which just checks the if the stock is valid and call it on text box leaving event..

2.For no results scenario,it depends on how you are showing results back(datareader,datatable..),you can check number of rows returned and if it is zero then you can show "The stock item is currently 'out of stock'."

If you want to handle this all at SQL level,i suggest below way,this is not inefficient provided you have right indexes..

create proc usp_getdata
(
@stockid int
)
As
Begin
IF Exists(
select FROM Stock
WHERE Stock.Category IN ('X', 'Y') and stockid=@stockid)
Begin
--Your valid code which returns results

End
Else 
Begin
return -1 --code for invalid 
End

End
0

I took a different approach by using a Common Table Expression to create a dummy 1 row table to left join against the Stock table. The left join allows you to check for null when the where clause is not satisfied, but it guarantees you will always get a row back.

Hopefully, I have understood your question and requirements - you can change to @StockId variable to test various conditions.

DECLARE @StockID INT =1;

WITH DummyTable AS 
(SELECT getdate() AS DummyColumn),

Stock
AS (
    SELECT 1 AS Id,
        'should find this' AS Description
        ,1 AS Quantity
        ,'X' AS Category
UNION
    SELECT 2 AS Id
        ,'no more' AS Description
        ,0 AS Quantity
        ,'Y' AS Category
UNION
    SELECT 3 AS Id
        ,'bad category or stock not found' AS Description
        ,0 AS Quantity
        ,'?' AS Category
    )
SELECT b.ID
    ,case 
        when b.Description is null then 'bad category or stock not found' 
        when b.Quantity=0 then 'Out Of Stock'
        else b.Description 
    end as description
    ,b.Quantity
FROM DummyTable a   --one row dummy table for left join
LEFT JOIN Stock b ON b.id = @StockID
    AND b.Category IN ('X','Y')
    AND b.Quantity >= 0
    AND b.ID = @StockID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.