Table A

id | food_id |  price
1  |   3    |    5
2  |   7    |    9
3  |   3    |    8

Table B

id  | drink_id | price | type_id
1   |  8       |  8    |  3
2   |  6       |  9    |  3
3   |  6       |  10   |  1

Table C

id(food_id) |  Name
3           |  Banana
7           |  Strawberry

I have 3 tables like this. I want the result of the query written with the stored procedure to be as follows.

column 1    
13  (select sum(price) from tableA where food_id = 3)

column 2  
2  (Select count(*) from  tableB where drink_id = 6)

column 3
9  (Select sum(price) from  tableB where drink_id = 6 and type_id = 3)

column 4
Banana (Select Name from tableA a left join tableC c On a.id = c.id) where a.id = 1)

Suppose there are millions of rows of data in these tables. How to write the best stored procedure?

CodePudding user response：

You can use your queries as subqueries.

For a stored procedure it isn't enough but who knows what you need it for

DECLARE @food_id INTEGER = 3;
DECLARE @drink_id int = 6;
DECLARE @type_id INTEGER = 3;
DEClARE @a_id int = 1;

SELECT
    (SELECT SUM(price) FROM tableA WHERE food_id = @food_id) col1,
    (SELECT COUNT(*) FROM tableB WHERE drink_id = @drink_id) col2,
    (SELECT SUM(price) FROM tableB WHERE drink_id = @drink_id AND type_id = 3) col3,
    (SELECT Name FROM tableA a 
     LEFT JOIN tableC c ON a.id = c.id 
     WHERE a.id = @a_id) col4

Output:

 CREATE PROCEDURE Getdata 
     @food_id AS INTEGER,
     @drink_id int,
     @type_id INTEGER, 
     @a_id int 
 AS 
 SELECT
 (select sum(price)from tableA where food_id=@food_id) col1,
 (Select count(*) from  tableB where drink_id=@drink_id) col2,
 (Select sum(price) from  tableB where drink_id=@drink_id and type_id=3) col3,
 (Select Name from tableA a left join tableC c On a.id = c.id where a.id = @a_id) col4


 EXEC Getdata @food_id = 3,@drink_id = 6,@type_id = 3,@a_id = 1;

Output:

db<>fiddle here

CodePudding user response：

NOTE: Not validated as I don't have your tables, but this format should work once you put it into an SP.

-- put this into an SP


-- delcare varaibles, probably should change them to match results (could be decimal or int depending on what your SUM does)
DECLARE @SumPriceFood AS VARCHAR(50)
DECLARE @CountDrink AS VARCHAR(50)
DECLARE @SumPriceDrink AS VARCHAR(50)
DECLARE @Name AS VARCHAR(50)

-- get your data (not tested these as they are your tables)
SELECT @SumPriceFood = SUM(price) from tableA where food_id = 3

SELECT @CountDrink = COUNT(*) from tableB where drink_id = 6

SELECT @SumPriceDrink = sum(price) from tableB where drink_id = 6 and type_id = 3

SELECT @Name = Name from tableA a left join tableC c On a.id = c.id where a.id = 1

-- now just select the variable values you populated above for your results
SELECT @SumPriceDrink AS SumPriceDrink, @CountDrink AS CountDrink, @SumPriceDrink AS SumPriceDrink, @Name AS Name