Sql-server – TSQL Query – Efficient

sql servert-sql

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;

Best Answer

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.

Related Question