I'm trying to do a stored procedure to update data in a couple of tables. I've written the following code:
and I receive the error message:
It won't let me create the stored procedure. If I remove the second query (DELETE...), then it works. Can you not have multiple INSERTs and DELETEs, etc. in one stored procedure?
I'm doing this in SQL Query Analyzer; using SQL 2000.
Thanks.
Code:
CREATE PROCEDURE procPostInvoice
@InvNumber nvarchar(15)
AS
--SET XACT_ABORT ON --if one fails, they all fail and all data is rolled back
--BEGIN TRANSACTION
--GO
INSERT INTO tblInvoiceHeader (InvoiceNum, InvoiceDate, InvType, JobID, SubTotal, Tax1, Tax2, Tax3, DetailLevel, Terms, CustContact, PO, Tax1Exempt, Tax2Exempt, Tax3Exempt, Tax1ExemptNumber, Tax2ExemptNumber, Tax3ExemptNumber)
SELECT InvoiceNum, InvoiceDate, InvType, JobID, SubTotal, Tax1, Tax2, Tax3, DetailLevel, Terms, CustContact, PO, Tax1Exempt, Tax2Exempt, Tax3Exempt, Tax1ExemptNumber, Tax2ExemptNumber, Tax3ExemptNumber
FROM tblInvBatchH
WHERE InvoiceNum = @InvNumber
GO
DELETE
FROM tblInvBatchH
WHERE InvoiceNum = @InvNumber
GO
--COMMIT TRANSACTION
--GO
and I receive the error message:
Code:
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@InvNumber'.
It won't let me create the stored procedure. If I remove the second query (DELETE...), then it works. Can you not have multiple INSERTs and DELETEs, etc. in one stored procedure?
I'm doing this in SQL Query Analyzer; using SQL 2000.
Thanks.