shaikhnadeem21
Member
- Joined
- May 11, 2005
- Messages
- 5
- Programming Experience
- Beginner
How to use multiple query in single stored procedure.
it cannot be formated sorry said:CREATE PROCEDURE GetOrdersByAcct (@iAcctKey integer) AS -- Retrieve Orders for a single customer -- Since we're querying the order table three times, -- let's create a table variable (@tOrderList) of all -- order keys for the specific customer. That way, we -- can JOIN off the temporary order list DECLARE @tOrderList TABLE (OrderKey integer) INSERT INTO @tOrderList SELECT OrderKey FROM OrderHdr WHERE AcctKey = @iAcctKey SELECT OH.*, AM.AcctName FROM OrderHdr OH JOIN AcctMast AM ON AM.AcctKey = OH.AcctKey JOIN @tOrderList TMP ON TMP.OrderKey = OH.OrderKey SELECT OD.*, PM.ProdName FROM OrderDtl OD JOIN ProdMast PM ON PM.ProdKey = OD.ProdKey JOIN @tOrderList TMP ON TMP.OrderKey = OD.OrderKey SELECT * FROM OrderNotes ON JOIN @tOrderList TMP ON TMP.OrderKey = ON.OrderKey GO
CREATE PROCEDURE ListPeople()
RESULT ( lname [/size][size=2][color=#0000ff]CHAR[/color][/size][size=2](36), fname [/size][size=2][color=#0000ff]CHAR[/color][/size][size=2](36) )
BEGIN
[/size][size=2][color=#0000ff]SELECT[/color][/size][size=2] emp_lname, emp_fname
FROM employee;
[/size][size=2][color=#0000ff]SELECT[/color][/size][size=2] lname, fname
FROM customer;
[/size][size=2][color=#0000ff]SELECT[/color][/size][size=2] last_name, first_name
FROM contact;
[/size][size=2][color=#0000ff]END