parameter variable error in procedure 1

[HowieG]

I'm trying to do a stored procedure to update data in a couple of tables. I've written the following 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:

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.

 

[gmmastros]

Remove the [!]GO[/!]. All of them.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

take out the go statments

I would point out that this looks like you are going to be doing record based processing called from another stored proc? If so be aware that this is usually an inefficent wat to do business. Better to do all the inserts as a group.

Questions about posting. See faq183-874

 

[SQLDenis]

take out all the GO's except for the last one GO is a batch terminator

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


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


DELETE 
FROM tblInvBatchH
WHERE InvoiceNum = @InvNumber


--COMMIT TRANSACTION
--GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[HowieG]

Wow, three answers each 1 second apart.

Thanks to you all. Taking out the GO's worked perfectly.

I would point out that this looks like you are going to be doing record based processing called from another stored proc? If so be aware that this is usually an inefficent wat to do business. Better to do all the inserts as a group.

I'm not sure I understand what you mean. This procedure is to be run from an Access application that allows the user to 'post' an invoice. By design, they can only post one invoice at a time. The full procedure actually has about 8 different insert, update and delete queries in it. I wanted to simplify it for this question.

Thanks again.

 

[SQLSister]

OK if they can only do one at a time, I withdraw my objection. But people often do this when they want to update or import thousands of records aat once and then wonder why their systems run slowly.

Questions about posting. See faq183-874