I'm trying to create a stored procedure in MS SQL Server 2000 that will add orders to two tables at a time. I've simplified the procedure here to better illustrate my problem. Basically, I want to pass parameters to the procedure and use them to insert new records into my tables. Each table has an identity column and I'm using @@identity to retrieve the last identity number from Table A and add it to Table B. I think the ASP is fine -- I'm adding the parameters via the Command object -- so I don't think that is the problem. I think the problem lies somewhere with the order in which I'm passing the variables. I've tried this several different ways and this is just my latest attempt.
Here is the procedure:
and i'm getting this error:
CREATE PROCEDURE dbo.sp_InsertTransaction
INSERT INTO orderstest(TransCode, TransTime, AuthCode, CustID)
VALUES(@TransCode, GETDATE(), 'test', 'test')
SELECT @TransactionID = @@identity
INSERT INTO orderstesttwo(ID) VALUES(@TransactionID)
Procedure 'sp_InsertTransaction' expects parameter '@TransactionID', which was not supplied.
Keep in mind, I have created the parameter @TransCode in my ASP code.