stored proc error

[krisc13]

I am trying to use the following stored procedure. I get an error on or around my INSERT statement.

CREATE PROCEDURE `f_agent`.`cust_prop` ()
BEGIN
  INSERT INTO `f_agent`.`customer__property`
  (`cust_prop_cust_id`, `cust_prop_prop_id`)
  SELECT `customer`.`cust_id` `cust_prop_cust_id`, `property`.`prop_id` `cust_prop_prop_id`
  FROM `f_agent`.`customer`
  INNER JOIN `f_agent`.`property`
  ON `customer`.`prev_cust_id` = `property`.`prev_cust_id`
END

All I want to do is populate a table with the customer ids and the property ids that have matching previous customer ids. Any assistance would be appreciated. Thank you!

 

[gmmastros]

You missed the AS

CREATE PROCEDURE [f_agent].[cust_prop] ()
[!]AS[/!]
BEGIN

Also, your 'back apostrophes' may cause a problem. Instead, you should use square brackets.

-George

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

 

[krisc13]

Thanks for your advice. I'm afraid I made all those changes and still get the same error. Or at least it's on the same line. Seems to be where I specify cust_prop_cust_id and cust_prop_prop_id.

 

[gmmastros]

This is how I would have written it.

CREATE PROCEDURE [f_agent].[cust_prop] 
As
BEGIN
  INSERT 
  INTO   f_agent.customer__property
         (cust_prop_cust_id, 
         cust_prop_prop_id)
  SELECT customer.cust_id, 
         property.prop_id
  FROM   f_agent.customer
         INNER JOIN f_agent.property
           ON customer.prev_cust_id = property.prev_cust_id
END

The apostrophes around table and field names are only necessary when you have a table (or field) this is a reserved name.

In your original query, cust_prop_cust_id and cust_prop_prop_id are considered aliases. You don't use aliases when inserting the data in to a table.

-George

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