1

I want to output data that will display various results depending on Type and/or Quantity values.

Each query calls the same table and returns the same columns. I was thinking of creating a UNION on each one. Or should I just make a single call to database, populate a table variable from this, and then query table variable to get the desired output?

Below a sample of queries. Thanks.

-- 1) display where type is 'detail'
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Detail')
ORDER BY Quantity DESC;

-- 2) display where type is 'current' and positive
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity >= 0
ORDER BY Quantity DESC;

-- sum positive values
SELECT SUM(Quantity)
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity >= 0
GROUP BY Type;

-- 3) displaywhere type is 'current' and negative
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity < 0
ORDER BY Quantity DESC;

-- sum negative values
SELECT SUM(Quantity)
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity < 0
GROUP BY Type;
  • Well - union can be fine, but I'm not sure it's doing waht you're after. Based on what you query in the snippet, you seem to be able to just query where type = Detail or type = current and then have two different sums over quantity on that query, leaving you only a single query. – Allan S. Hansen Jan 16 '14 at 11:52
2

In my personal opinion, it seems as all of those queries have very separate requirements and output. I would personally keep them separate to the end. In other words, what are you going to gain out of filling a temporary object with a key/value pair of the output of 5 unrelated queries?

I'd wrap them each in a stored procedure, parameterize what I can to make code reusable, and then just make individual calls to each whenever I needed to. I like to follow the principle that a logical piece of code (stored proc, method, etc.) should do just one thing.

So the approach I would take is something like this:

create proc dbo.GetDetail
as
    SELECT Type, Description, Country, Quantity
    FROM UserHoldings
    WHERE (Type = 'Detail')
    ORDER BY Quantity DESC;
go

create proc dbo.GetCurrent
    @Quantity int = 0
as
    SELECT Type, Description, Country, Quantity
    FROM UserHoldings
    WHERE (Type = 'Current')
    AND Quantity >= @Quantity
    ORDER BY Quantity DESC;
go

So on and so forth...

That way when you needed the data, you'd make your calls:

exec dbo.GetDetail;
exec dbo.GetCurrent;
exec dbo.GetCurrent @Quantity = 10;

Why this particular approach? Because I think this scales well. It's the way I typically approach database development. I'm completely fine with having a bunch of small pieces of executable code. It beats having a few big ones, and having to modify accordingly. That doesn't grow.

This is my opinion, and I'm looking forward to seeing what others would do in this scenario.

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.