Stored Procedure Running each part of the code 1

[collierd2]

Hello

Using SQL 2000, I have the following code:

CREATE PROCEDURE spDimCustomerAccountUpdate AS


CREATE TABLE #temp ( rn INT IDENTITY (1, 1), 
Branch_SK int,
CustomerBranchID_AT nvarchar(10),
CustomerBranch_AT nvarchar(30),
CustomerDivisionID_AT nvarchar(10),
CustomerDivision_AT nvarchar(30),
Salesman_SK int,
CustomerDefaultSalesmanID_AT nvarchar(10), 
CustomerDefaultSalesman_AT nvarchar(30))
go
 
-- Put distinct budget records into temporary table
INSERT #temp (Branch_SK, CustomerBranchID_AT, CustomerBranch_AT, CustomerDivisionID_AT, CustomerDivision_AT,
	Salesman_SK, CustomerDefaultSalesmanID_AT, CustomerDefaultSalesman_AT)
select distinct bud.Branch_SK, branch.BranchID_AT, branch.Branch_AT, 
	branch.DivisionID_AT, branch.Division_AT, bud.Salesman_SK, 'X', 'Mr x' from FactSalesBudget bud
	left outer join DimBranch branch on bud.Branch_SK = branch.Branch_SK
go

declare @loop integer
declare @totalrecords integer
SET @loop = 1
SET @totalrecords = (select count(*) from #temp)
 
-- Run Code to update Customer table
WHILE @loop <= @totalrecords
BEGIN

	insert into DimCustomerAccount
	select @loop+99, '', 'X' + cast(@loop+99 as varchar(5)), 'Missing Level', '2000-01-01', 0, '', '', 
		CustomerBranchID_AT, CustomerBranch_AT, 
		CustomerDivisionID_AT, CustomerDivision_AT, 
		CustomerDefaultSalesmanID_AT, CustomerDefaultSalesman_AT,
		'X', 'Mr X', 0, '' from #temp
		where rn = @loop
	SET @loop = @loop + 1

END
go

-- Update FactSalesBudget so CustomerAccount_SK matches that defined in DimCustomerAccount
update FactSalesBudget
	set CustomerAccount_SK = tp.rn + 99
from FactSalesBudget factb
left outer join #temp tp on (factb.Branch_SK = tp.Branch_SK and factb.Salesman_SK = tp.Salesman_SK)
where factb.CustomerAccount_SK = 0
go

No matter what I try, it just seems to execute each part of the code rather than create a SP

Does anybody know where I am going wrong

Thanks

 

[gmmastros]

You cannot have GO inside a stored procedure. Remove all the GO's and try again.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[collierd2]

Strange, initially, I wrote the code without the gos and it failed.
Just removed them all and it worked without a problem

Thanks for the help

Damian.