stored procedure

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
Hi,
A single MSSQL stored procedure can easily return multiple result sets. The general rule is that any SQL SELECT statement in a stored procedure with no explicit output declaration (to a table, variable, etc.) returns the results to the calling routine.

this is an example for a simple stored procedure to return multiple result sets that i found on net ...


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


ok this is the one small that i succeed to format ... hahaha :)

VB.NET:
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

[/color]
 
Last edited:
Top Bottom