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

    Unanswered: 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?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would you mind posting the procedure code, actual error message and specific question?

    Because the only answer to your "any clues?" question is your wrote a procedure that doesn't work.

  3. #3
    Join Date
    Nov 2005
    Posts
    4
    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')

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SQL Server, you say ... unfortunatelly, that's beyond my knowledge. I could suggest something if it was Oracle DB, but "customer_id INT IDENTITY (1000, 1)" really means nothing to me. I don't even know whether sequences and DB triggers work the same way in those databases (I'd say not).

    You'd probably get faster (and a better) answer if you posted the question on the SQL Server forum instead of here; I guess moderator(s) will move this thread there for you.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by xoxo123

    any clues?
    Forgive the presumption (I looked at your join date and the nature of your question), but this looks an awful lot like a homework question.

    In any case, your answer can be found in the SQL Books on Line (BOL) under the IDENTITY (property) entry.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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