execute multiple commands in one stored procedure

[yshie]

is it possible to create multiple sql commands in one stored procedure?
in my button click, i have to insert values in two tables so it is one way i thought of but i don't know if its correct.

CREATE PROCEDURE dbo.SP1

(
@OrderDate DATETIME,
@OrderID INT,
@ProdID INT
)

AS
/* SET NOCOUNT ON */
INSERT INTO Orders
VALUES(@OrderDate) //this is the insert command for my first table i don't insert orderID(PK) here because it is autonumber

SELECT @OrderID=OrderID
FROM Orders //i don't know if this command is correct but what i want to do is get the orderID based on the above statement

INSERT INTO OrderDetails
VALUES(@OrderID, @MenuID) //this is the insert command for the second table

RETURN

this stored procedure is working good for the first table but not when i added insert on the second table. maybe because of my Select statement

[exterminator]

Whats your database? It would be a good thing to wrap both the queries into one transaction so that if the first one succeeds and second fails, you rollback the first one and then return an error rather than leaving the relational data inconsistent. To get the generated auto ID - you can do select @@IDENTITY (see if your database supports this). Make sure that this forms part of the same transaction above. If the transaction blocks differ - in case of concurrent inserts - this may return a wrong value for you.

[yshie]

i use sql server.. im new into this. i'll research on @@identity! tnx..
i'll report what will happen*wink*

[yshie]

ok, its becoming clearer..
but what will i put in the parameter's value in my code?
cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = ???

[exterminator]

You don't need to pass the order ID parameter to the stored procedure. It can be got from within the stored procedure itself for the subsequent insert. Also, take a look at this article to understand about the transaction management that I mentioned earlier - http://www.4guysfromrolla.com/webtech/080305-1.shtml

[TheCPUWizard]

Regarding the original question. With SQLServer (and other DBMS) stored procedures can be extremely complex. I have seen entire "applications" (minus the UI, but with ALL the logic) in stored procedures.

[yshie]

i got it now! tnx guys!

[cjard]

doesnt sql server have something like

INSERT INTO x VALUES (1,2,3) RETURNING x.ID INTO myIN_OUT_IDParameter

where you dont have to do the separate select?

this isnt ado.net; please post future queries about sprocs in the database section..