ALTER TABLE in stored procedure not working

[NathanGriffiths]

I have created the following stored procedure but when executed it fails on the update statement indicated due to the LastOrderValue and LastPaymentMethod columns not being present in tmp_OrderDates, apparently because the ALTER TABLE statement has not worked - I am correct in this interpretation and can anyone point out where I'm going wrong?

thanks,

Nathan


CREATE PROCEDURE spTransactions_2 AS


UPDATE IndSummary SET orderfreq_25_36 = null,
lastordervalue = null,
lastorderdate = null,
firstorderdate = null,
lastpaymethod = null

IF EXISTS( SELECT * FROM Information_Schema.Tables WHERE table_name = 'tmp_OrderDates' )
BEGIN
DROP TABLE tmp_OrderDates

SELECT
UIN,
Brand,
MAX(OrderDate) AS MaxOrderDate,
MIN(OrderDate) AS MinOrderDate
INTO tmp_OrderDates
FROM Orders
GROUP BY UIN, Brand
ORDER BY UIN, Brand
END

ALTER TABLE tmp_OrderDates
ADD
LastOrderValue SMALLMONEY,
LastPaymentMethod VARCHAR(15)

-- The following statement fails !
UPDATE t
SET t.LastOrderValue = rderValue, t.LastPaymentMethod = o.PaymentMethod
FROM tmp_OrderDates t
JOIN Orders o ON t.UIN = o.UIN AND t.Brand = o.Brand AND t.MaxOrderDate = rderDate


CREATE INDEX idx_tmp_OrderDates_UINBrand ON tmp_OrderDates(UIN, Brand)

UPDATE i
SET i.LastOrderDate = t.MaxOrderDate,
i.FirstOrderDate = t.MinOrderDate,
i.LastOrderValue = t.LastOrderValue,
i.LastPayMethod = t.LastPaymentMethod
FROM IndSummary i
JOIN tmp_OrderDates t ON i.UIN = t.UIN AND i.Brand = t.Brand
GO

 

[foxdev]

This may or may not be related, but I'm a bit confused about your logic:

[tt]IF (temp table) EXISTS ...
BEGIN
DROP TABLE tmp_OrderDates

SELECT ...
INTO tmp_OrderDates
END[/tt]

What if the table doesn't exist? There doesn't seem to be any code to create it. If it exists, you are dropping and recreating it, but not doing anything if it does not exist. --------------
Low Cost Prints:

http://www.lowcostprints.com

 

[NathanGriffiths]

Sorry, the BEGIN and END there are erroneous, that section should read;

IF EXISTS( SELECT * FROM Information_Schema.Tables WHERE table_name = 'tmp_OrderDates' )
DROP TABLE tmp_OrderDates

SELECT
UIN,
Brand,
MAX(OrderDate) AS MaxOrderDate,
MIN(OrderDate) AS MinOrderDate
INTO tmp_OrderDates
FROM Orders
GROUP BY UIN, Brand
ORDER BY UIN, Brand

i.e. if the table exists, it is dropped before it is created in the SELECT INTO statement

I don't think that is the cause of the problem though...

 

[SQLSister]

Haven't had time to really go through the code and see if I see a problem, but I do know I have had that kind of problem in the past when I put everything in one batch as you did. Try putting a GO statement ater the ALTER TABLE and see if that will fix the problem.

 

[tlbroadbent]

Nathan,

Replace the Alter Table statement with the following.

Exec('ALTER TABLE tmp_OrderDates
ADD LastOrderValue SMALLMONEY,
LastPaymentMethod VARCHAR(15)')

SQLSister,

If Nathan puts a GO in the code after the Alter Table statement, that will effectively mark the end of the Create Procedure statement and the code following will not be part of the SP. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.