Multiple inserts in one stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple inserts in one stored procedure

Author

Topic

Skydolphin
Starting Member

43 Posts

Posted - 2007-07-16 : 13:09:25

Can you put multiple inserts into one stored procedure? I am trying to reduce the number of SQL calls I need in some code. I wrote a procedure that creates a record in the main table, returns the identity of the record and then inserts data into corresponding tables using the returned ID. I keep getting this error, "Must declare the scalar variable '@NEWID'".

Here is my code. What am I doing wrong?

CREATE PROCEDURE tblCreateQuoteDetails
@OpportunityID nvarchar(256),
@QuoteType nvarchar(50),
@Status int,
@PK_New int OUTPUT,
@QuoteNumber nvarchar(50),
@Region int,
@Currency nvarchar(10),
@CreateDate datetime,
@QuoteValidDate datetime,
@PaymentTerms nvarchar(10),
@TaxType nvarchar(10),
@TaxPercent nvarchar(10),
@DiscountPercent nvarchar(50),
@DiscountAmount nvarchar(50),
@CreatedByID int,
@CustomerType int,
@CustomerName nvarchar(256),
@CustomerAddress1 nvarchar(256),
@CustomerAddress2 nvarchar(256),
@CustomerCity nvarchar(256),
@CustomerStateProvence nvarchar(256),
@CustomerPostalCode nvarchar(50),
@CustomerCountry nvarchar(50),
@CustomerContactName nvarchar(256),
@CustomerContactTitle nvarchar(256),
@MaintenancePercent nvarchar(50),
@MaintenancePeriod int,
@MaintenanceBegin datetime,
@MaintenanceEnd datetime,
@MaintenanceListPrice nvarchar(50),
@MaintenanceSalePrice nvarchar(50)

AS
Declare @NEWID int
insert into tblQuotes (OpportunityID, QuoteType, Status) values (@OpportunityID, @QuoteType, @Status)

SELECT @PK_New = @@IDENTITY
Set @NEWID = @PK_New
GO

insert into tblQuoteDetails (QuoteID, QuoteNumber, Region, Currency, CreateDate, QuoteValidDate, PaymentTerms, DiscountPercent, DiscountAmount, CreatedByID, TaxType, TaxPercent) values (@NEWID, @QuoteNumber, @Region, @Currency, @CreateDate, @QuoteValidDate, @PaymentTerms, @DiscountPercent, @DiscountAmount, @CreatedByID, @TaxType, @TaxPercent)
GO

insert into tblQuoteCustomerDetails (QuoteID, CustomerType, CustomerName, CustomerAddress1, CustomerAddress2, CustomerCity, CustomerStateProvence, CustomerPostalCode, CustomerCountry, CustomerContactName, CustomerContactTitle) values (@NEWID, @CustomerType, @CustomerName, @CustomerAddress1, @CustomerAddress2, @CustomerCity, @CustomerStateProvence, @CustomerPostalCode, @CustomerCountry, @CustomerContactName, @CustomerContactTitle)
GO

insert into tblQuoteMaintenanceDetails (MaintenancePercent, MaintenancePeriod, MaintenanceBegin, MaintenanceEnd, MaintenanceListPrice, MaintenanceSalePrice) values (@NEWID, @MaintenancePercent, @MaintenancePeriod, @MaintenanceBegin, @MaintenanceEnd, @MaintenanceListPrice, @MaintenanceSalePrice)
GO

Rhonda

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-16 : 13:11:52

Ofcourse you can put multiple T-SQL statements (whether INSERTs, UPDATEs , DELETEs or any other..)
Remove all the GO's. GO indicates termination of a batch of SQL. So any variables declared prior to it will not be accessible.
Use SCOPE_IDENTITY() instead of @@IDentity. Read up books on line for the differences.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Skydolphin
Starting Member

43 Posts

Posted - 2007-07-16 : 13:23:46

That worked thanks.

Rhonda

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources