2 STP inside a 3rd STP? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

2 STP inside a 3rd STP?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-08 : 09:58:06

I need help making a third stored procedure that utilize 2 stored procedure that I already created. Below are the 2 stored procedure that I want to use in a third stored procedure that will use a temp table and combine it together.

Store Procedure 1:

SELECT DISTINCT CUSTOMER.PRODUCT_ID, SUM(CUSTOMER.AMOUNT) AS
[SalesAmount]
FROM CUSTOMER INNER JOIN
CUSTOMER_SALE ON CUSTOMER.ORDER_ID = CUSTOMER_SALE.ORDER_ID
WHERE (CUSTOMER.PRODUCT_ID IS NOT NULL) AND (CUSTOMER.DATE BETWEEN @STARTDATE AND @ENDDATE)
GROUP BY CUSTOMER.PRODUCT_ID
ORDER BY CUSTOMER.PRODUCT_ID

Stored Procedure 2:
SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
FROM INV_TRANSACTION
WHERE (TRANS_ID IN
(SELECT MAX(TRANS_ID)
FROM CUSTOMER, INV_TRANSACTION, CUSTOMER_SALE
WHERE CUSTOMER.ORDER_ID = INV_TRANSACTION.ORDER_ID AND
CUSTOMER_SALE.ORDER_ID = CUSTOMER.ORDER_ID AND CUSTOMER.PRODUCT_ID = @PRODUCTID AND (CUSTOMER.DATE BETWEEN @STARTDATE AND @ENDDATE)
GROUP BY CUSTOMER.ORDER_ID))

Here's my attempt, but not working:

DECLARE @TEMPTABLE TABLE(CUSTOMER_ID NVARCHAR(40), AMOUNT VARCHAR(30) , COST VARCHAR(30))
INSERT INTO @TEMPTABLE(CUSTOMER_ID, AMOUNT) VALUES STOREDPROCEDURE1
INSERT INTO @TEMPTABLE(COST) VALUES STOREDPROCEDURE2
SELECT * FROM @TEMPTABLE

For the storeprocedure, I'm not sure of what to put there. Probably the value that I want to insert, but how would I say from stored procedure 1 or 2 field to be insert.

Please help. Thanks in advance.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 10:11:46

DECLARE @TEMPTABLE TABLE(CUSTOMER_ID NVARCHAR(40), AMOUNT VARCHAR(30) , COST VARCHAR(30))

INSERT INTO @TEMPTABLE(CUSTOMER_ID, AMOUNT)
Exec STOREDPROCEDURE1

INSERT INTO @TEMPTABLE(COST)
Exec STOREDPROCEDURE2

SELECT * FROM @TEMPTABLE

Although, I don't think this is really what you are trying to do. The second stored procedure will insert additional records into the @temptable with only the cost filled. It will not update the records inserted by sproc1.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-08 : 14:22:03

Thanks for the example. Here's what I'm trying to do with these two stored procedure and helpfully you can shine some lights.

I want to display the first stored procedure, PRODUCT_ID and AMOUNT. Then using the PRODUCT_ID in the where clause of the Stored Procedure 2, pull the correct data that matches the PRODUCT_ID and have it sum.

YOu are correct that the second stored procedure will just insert extra records without the first stored procedure's records.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 15:19:37

Why does it have to be two sprocs?

It seems like you would do something more like:


Insert Into #results1
Select product_Id, amount from <table1> Where <conditions1 are met>

Insert Into #results2
Select product_Id, amount, cost 
From <sometable> Inner Join #results1 On <sometable>.product_Id = #results1.product_Id 

Select * from #results2

Does that make any sense?

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-09 : 09:57:15

Thanks, that does make more sense.

Just a couple of questions since I just started working with T-SQL two months ago.

1. For the insert into the #results, don't I need to declare fields for it to insert to?

Example:

INSERT INTO #Results1(Product_ID NVARCHAR(80), Amount VARCHAR(40))
SELECT.....

2. My assumption is that #Result1 is for the first query and #Result2 is for the second query. Not sure why #Result2 has 3 fields when I only have 1 field for the second query.

Example:
Query 1:
SELECT DISTINCT PRODUCT_ID, SUM(AMOUNT) AS [SALES AMOUNT]

QUERY 2:
SELECT SUM(MAT_COST+LAB_COST+BUR_COST+SER_COST) AS [TOTAL COST]

3. In order to reference the PRODUCT_ID from query 1 to be use with query 2, I have a parameter. Do I still need that parameter in this scenerio?

Sorry for asking so much questions, but I'm fairly new to SQL and seeking some expert help from the forum.

Thanks again.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-09 : 14:03:20

What is the final result that you want look like?

I thought that you wanted to look up addition information for the results of the first query. This is why the 2nd query had 3 columns. You may not even need 2 queries, it just depends on what you are trying to get, and what the source data looks like.

The more relevant info on source and results, the better the answer

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-09 : 17:17:50

I do need two queries because when I combined the two together I do not get expected result:

Here's the output for each one:

Query 1:

PRODUCT_ID SALEAMOUNT
----------------------
METALFAB 899.00
PLASTIC 2342.00
STEELBAR 223.00

Query 2:
TOTALCOST
----------
1800.00

Query 2 will just sum everything up unless I use the parameters in the where clause of query 2 to reference it from query 1.

Expected Result:

PRODUCT_ID SALEAMOUNT TOTALCOST
---------------------------------
METALFAB 899.00 500.00
PLASTIC 2342.00 1200.00
STEELBAR 223.00 100.00

I'm not sure if it's possible to reference an object from one query to another. For example I want to use the field Product_ID in query 1 as a reference in the where clause of query 2, the @ProductID parameter.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-09 : 18:36:32

So what does the data look like before you do any queries? I'm still not convinced you need two queries.

And you can also join the results of the first query in the second query.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-10 : 11:03:24

Without using queries, there are tons of records from the table.

PRODUCT_ID SALEAMOUNT TOTALCOST
---------------------------------
METALFAB 450.00 250.00
METALFAB 324.00 234.00
METALFA 523.00 234.00
PLASTIC 423.00 345.00
PLASTIC 342.99 243.00
STEELBAR 234.00 232.00
STEELBAR 343.00 154.00

That's what it would look like if I do a select all query.

If I join the two togther this is what I get, which seems like there are more records:

PRODUCT_ID SALEAMOUNT TOTALCOST
---------------------------------
METALFAB 348983.00 34333.00
PLASTIC 76653.00 44267.00
STEELBAR 9874.00 4432.00

The intended output should be like:

Expected Result:

PRODUCT_ID SALEAMOUNT TOTALCOST
---------------------------------
METALFAB 899.00 500.00
PLASTIC 2342.00 1200.00
STEELBAR 223.00 100.00

I can get this in a report tool that I use, called Reporting Services. If I use the field Product_ID from query 1 as a parameter for Query 2 @PRODUCTID in a subreport, then it gets the intended output. The problem with this is when I want to sum the subreport and have it displayed on the main page.

So I was wonder if there's a way to do it in the stored procedure level where I can create a temp table, that will be able to do the same thing. Insert into 2 tables like what you suggested, but have query2(Temptable2) reference the parameter from query1(Temptable1) field Product_ID. And then have a final select statement displaying all 3 fields.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-10 : 11:06:50

So are you saying you want to group by product_Id and sum the saleAmount and sum the totalCost?

Select Product_Id, saleAmount = sum(saleAmount), totalCost = sum(totalCost)
From <yourTable>
Group By Product_Id

Thats what it looks like to me. I'm sorry if I'm being dense.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-10 : 16:45:48

Thanks for being patience with me. I'm kind of new to SQL Query so I'm sort of running out of ideas on what to try. In ASP, I can have 2 queries and reference one field from one query to the other. I don't know if it's possible to do it within the query level. Here's an example of what I do in ASP.

First Query:

SELECT R.Part_ID, R.Cost, PO.O_Status
FROM Request R, Purchase_Order PO, Request_Binary RB
WHERE R.Order_Base_ID = '"&Order_base_ID&"'
AND R.Part_ID *= RB.Order_Base_ID
AND R.Sub_ID IS NULL

Second Query:
Function GETPO(Order_base_ID, Part_ID, Sub_ID)
SELECT TOP 1 S.Supply_ID, S.Supply_Req_No, PO.Status
FROM Supply S, Purchose_Order PO, Request R
WHERE S.Supply_ID = '"&Order_base_ID&"'
AND S.Demand_ID = '"&Part_ID&"'
AND R.Request_ID = '"&Sub_ID&"'

As you can see, I reference the first query fields for the second query. I'm not sure if there's a way to do it like this in Query Analyzer.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-10 : 17:34:57

One query could do this. It might look something like this:


Select TOP 1 
	S.Supply_ID, 
	S.Supply_Req_No, 
	PO.Status
FROM Supply S, Purchase_Order PO, Request R
Inner Join
	(
	SELECT 
		R.Order_Base_ID
		R.Part_ID, 
		R.Cost, 
		PO.O_Status,
		R.Sub_ID
	FROM Request R, Purchase_Order PO, Request_Binary RB  
	WHERE R.Order_Base_ID = @Order_base_Id
	AND R.Part_ID *= RB.Order_Base_ID
	AND R.Sub_ID IS NULL
	) q1
On S.Supply_ID = q1.Order_Base_ID
AND S.Demand_ID = q1.Part_ID
AND R.Request_ID = q1.Sub_Id

I highlighted the Purchase_Order table, as you have no conditions on it. You really should look into using a join or two. You probably could include the supply table in the first query and simplify it further. I also highlighted the R.Sub_Id because in the first query you required it to be null, and then it is a requirement for the second query. Thus I was confused about what it should be.

Let me know if I'm making any sense or not

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-11 : 17:17:33

Yeah!!! That makes sense. Thanks so much!!!

I will work on the example you provided. Only one little problem is I'm getting this error when executing the query.

"Outer join operators cannot be specified in a query containing joined tables."

I tried using Inner Join, Left Outer Join, and Right Outer join, but still gives me the same error.

Any ideas?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-11 : 17:22:50

Well the problem is the way I combined it was with a 'subquery'. Since the subquery is almost using the exact same tables, you probably could do this from one query (no subqueries). Try to join all of the tables you need together in one query and go from there. Post what you got if you get stuck.

Good Luck!

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-02-14 : 09:18:18

Thanks for that example. I think that it might've work, but I'm getting that error that I post earlier. I also try combining it, but I can't seem to reference it like the example you provided without using subquery.

Can you provide me with an example by combining it and reference it like what you did with the above example?

Thanks again. I've been working on getting this work for a week now, and I really appreciate the time you spent on helping me out.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-14 : 13:07:46

Can you provide the relevant columns of the tables you are working with. A few sample rows for each table and desired result layout would help a ton. It is hard to guess how the tables relate together. Thanks!

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources