Select - Update - Insert

[TomBarrand]

I would like to select all of the records that meet the criteria below, then increment the Revision_No by 1 and insert this as a new record into the Purch_Ord_Hdr table.

SELECT (Purch_Ord_No, Revision_No, Status, Curr_ISO_Code, Retailer_No, Retailer_Contact, Retailer_Email, Ben_No, Ben_Contact, Ben_Email, Sales_Agent_No, Sales_Agent_Contact, Sales_Agent_Email, User_No, Factory_No, Factory_Contact, Factory_Email, Business_Unit, Terms, Freight_Mode, Season, Purch_Ord_Date, Auth_Notes, Assumptions, Quantity_Total, Ord_Value_Total, Expiry_Date)
FROM Purch_Ord_Hdr
where Purch_Ord_No = @PONo

I have tried to use a stored procedure with the syntax below, but I get errors.

Any ideas would be appreciated

CREATE PROCEDURE sp_NewRevision

@PONo INT,
@PORevNo INT,
@NewPORevNo INT

AS
begin
select @NewPORevNo = @PORevNo + 1

end

INSERT into Purch_Ord_Hdr (Purch_Ord_No, @NewPORevNo, Status, Curr_ISO_Code, Retailer_No, Retailer_Contact, Retailer_Email, Ben_No, Ben_Contact, Ben_Email, Sales_Agent_No, Sales_Agent_Contact, Sales_Agent_Email, User_No, Factory_No, Factory_Contact, Factory_Email, Business_Unit, Terms, Freight_Mode, Season, Purch_Ord_Date, Auth_Notes, Assumptions, Quantity_Total, Ord_Value_Total, Expiry_Date)
SELECT (Purch_Ord_No, Revision_No, Status, Curr_ISO_Code, Retailer_No, Retailer_Contact, Retailer_Email, Ben_No, Ben_Contact, Ben_Email, Sales_Agent_No, Sales_Agent_Contact, Sales_Agent_Email, User_No, Factory_No, Factory_Contact, Factory_Email, Business_Unit, Terms, Freight_Mode, Season, Purch_Ord_Date, Auth_Notes, Assumptions, Quantity_Total, Ord_Value_Total, Expiry_Date)
FROM Purch_Ord_Hdr
where Purch_Ord_No = @PONo

 

[tlbroadbent]

I note a couple of problems.

1) the variable @NewPORevNo should be used in the Select statement rather than the list of columns to insert. You have revered the column name and the variable.
2) The same variable should be declared as a local parameter in the SP rather than a parameter passed to the SP.

All other changes are merely cosmetic to make the SP more readable.

CREATE PROCEDURE sp_NewRevision

@PONo INT,
@PORevNo INT

AS

Begin

Declare @NewPORevNo INT

Select @NewPORevNo = @PORevNo + 1

INSERT into Purch_Ord_Hdr
(Purch_Ord_No, Revision_No,
Status, Curr_ISO_Code,
Retailer_No, Retailer_Contact,
Retailer_Email, Ben_No,
Ben_Contact, Ben_Email,
Sales_Agent_No, Sales_Agent_Contact,
Sales_Agent_Email, User_No,
Factory_No, Factory_Contact,
Factory_Email, Business_Unit,
Terms, Freight_Mode,
Season, Purch_Ord_Date,
Auth_Notes, Assumptions,
Quantity_Total, Ord_Value_Total,
Expiry_Date)

SELECT
Purch_Ord_No, @NewPORevNo,
Status, Curr_ISO_Code,
Retailer_No, Retailer_Contact,
Retailer_Email, Ben_No,
Ben_Contact, Ben_Email,
Sales_Agent_No, Sales_Agent_Contact,
Sales_Agent_Email, User_No,
Factory_No, Factory_Contact,
Factory_Email, Business_Unit,
Terms, Freight_Mode,
Season, Purch_Ord_Date,
Auth_Notes, Assumptions,
Quantity_Total, Ord_Value_Total,
Expiry_Date)

FROM Purch_Ord_Hdr
WHERE Purch_Ord_No = @PONo

End Terry L. Broadbent
Programming and Computing Resources

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

 

[TomBarrand]

I am using the following code within the stored procedure, but I get the following error message

The select list for the insert statement contains fewer items than the insert list. The number of select values must match the number of insert columns.

Any ideas?

Thanks

CREATE PROCEDURE sp_NewRevision

@PONo INT,
@PORevNo INT

AS

Begin

Declare @NewPORevNo INT

Select @NewPORevNo = @PORevNo + 1

INSERT into Purch_Ord_Hdr
(Purch_Ord_No,
Revision_No,
Status,
Curr_ISO_Code,
Retailer_No,
Retailer_Contact,
Retailer_Email,
Ben_No,
Ben_Contact,
Ben_Email,
Sales_Agent_No,
Sales_Agent_Contact,
Sales_Agent_Email,
User_No,
Factory_No,
Factory_Contact,
Factory_Email,
Business_Unit,
Terms,
Freight_Mode,
Season,
Purch_Ord_Date,
Auth_Notes,
Assumptions,
Quantity_Total,
Ord_Value_Total,
Expiry_Date)

SELECT
('Purch_Ord_No,' +
'Revision_No,' +
'Status,' +
'Curr_ISO_Code,' +
'Retailer_No,' +
'Retailer_Contact,' +
'Retailer_Email,' +
'Ben_No,' +
'Ben_Contact,' +
'Ben_Email,' +
'Sales_Agent_No,' +
'Sales_Agent_Contact,' +
'Sales_Agent_Email,' +
'User_No,' +
'Factory_No,' +
'Factory_Contact,' +
'Factory_Email,' +
'Business_Unit,' +
'Terms,' +
'Freight_Mode,' +
'Season,' +
'Purch_Ord_Date,' +
'Auth_Notes,' +
'Assumptions,' +
'Quantity_Total,' +
'Ord_Value_Total,' +
'Expiry_Date')

FROM Purch_Ord_Hdr
WHERE Purch_Ord_No = @PONo

End

 

[tlbroadbent]

Remove the ' around the column names and the + from the Select list. Why did you put them there? You are not inserting literals. You want to insert the values in the columns. Terry L. Broadbent
Programming and Computing Resources

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