Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Unanswered: procedure creation

    i was asked to post this here..

    _----------------------------------------


    procedure creation
    I have to create a Procedure called customer_insert which inserts a record in the customer table. The input to the procedure should be all attributes of customer table except customer_id. This Procedure should use a sequence to generate a new customer_id when it is inserting a new record in the customer table. The rule for generating customer_id is that the minimum customer_id should be 1000 and customer_id should be incremented by 1 for every new record

    this is the cust table created
    create table CUSTOMER (
    CUSTOMER_ID NUMBER(6) NOT NULL,
    NAME VARCHAR2(45),
    ADDRESS VARCHAR2(40),
    CITY VARCHAR2(30),
    STATE VARCHAR2(2),
    ZIP_CODE VARCHAR2(9),
    AREA_CODE NUMBER(3),
    PHONE_NUMBER NUMBER(7),
    SALESPERSON_ID NUMBER(4),
    CREDIT_LIMIT NUMBER(9,2),
    COMMENTS VARCHAR2(256));

    I thought of creating a sequence first and then use the sequence inside the procedure to create a new customer_id ....didnt workkk...

    any clues?
    i thought of using identity property in the field so that SQL server automaticaly assigns a unique value to the record. I modified the table structure below. & tried to execute this.

    create table CUSTOMER
    (
    CUSTOMER_ID int identity(1000,1),
    NAME VARCHAR2(45),
    ADDRESS VARCHAR2(40),
    CITY VARCHAR2(30),
    STATE VARCHAR2(2),
    ZIP_CODE VARCHAR2(9),
    AREA_CODE NUMBER(3),
    PHONE_NUMBER NUMBER(7),
    SALESPERSON_ID NUMBER(4),
    CREDIT_LIMIT NUMBER(9,2),
    COMMENTS VARCHAR2(256)
    )

    But while writing insert statement didnt specify this column.

    Like

    Insert into CUSTOMER (NAME ,ADDRESS ,CITY ,STATE ,ZIP_CODE ,AREA_CODE ,PHONE_NUMBER,SALESPERSON_ID , CREDIT_LIMIT ,COMMENTS )
    values('xoxo','planet','earth','jupiter','1234','2 13',31231,231,1231,'1231')


    not working.....!!?!?!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, that is the way you should do it. What didn't work about it? Any error messages?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by xoxo123
    create table CUSTOMER
    ([...]
    CITY VARCHAR2(30),
    STATE VARCHAR2(2),
    ZIP_CODE VARCHAR2(9),
    [...])

    Insert into CUSTOMER (NAME ,ADDRESS ,CITY ,STATE ,ZIP_CODE ,AREA_CODE ,PHONE_NUMBER,SALESPERSON_ID , CREDIT_LIMIT ,COMMENTS )
    values('xoxo','planet','earth','jupiter','1234','2 13',31231,231,1231,'1231')
    This particular INSERT won't work because 'jupiter' won't fit into the varchar(2) column.
    Same for fitting '2 13' into the number(3) area code column.

    Fix that, and try again.
    And, as blindman suggests, an error message is much more useful than just writing 'not working.....!?!?!?'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •