locked
Create A Stored procedure RRS feed

  • Question

  • Table Structure: (BANKING APPLICATION)
         
    Customer (This table stores the customer information)

    CustomerID    

    UserID             (For authentication purpose)

    Name

    Gender             (M, F)

    DateOfBirth

    Address

                City

    State

    Pin

    Telephone

    Fax

    Email


    Create a Stored Procedure/Function for generating the new customer ID,The StoredProcedure should return the next customer ID.

     

    Tuesday, October 27, 2009 5:06 AM

Answers

  • create

     

    procedure Proc_Customerid

    @VarCustomerID

    varchar(50) output

    as

     

    select @VarCustomerID=max(isnull(CustomerID,0)) from dbo.Customer

     

    set

     

    @VarCustomerID=@VarCustomerID+1

     

    GO


    Lakshman
    • Proposed as answer by Bharani 3010 Tuesday, October 27, 2009 8:33 AM
    • Marked as answer by Kalman Toth Saturday, October 31, 2009 2:19 PM
    Tuesday, October 27, 2009 6:39 AM

All replies

  • What seems to be a problem here?

    Check http://blogs.lessthandot.com/index.php/WebDev/WebDesignGraphicsStyling/how-to-insert-information-into-multiple- for a similar procedure (though a bit more complex).
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    Tuesday, October 27, 2009 5:27 AM

  • "Create a Stored Procedure/Function for generating the new customer ID,The StoredProcedure should return the next customer ID
    .

    Provide bit more info or follow the link :

    http://www.sqlteam.com/article/stored-procedures-returning-data 

    Tuesday, October 27, 2009 5:35 AM
  • Hi,

    Write the logic of creating your next customer id in a stored procedure or function and set it to an output variable in that stored procedure or function.

    Are you facing any issues in logic of creating the customer id in any specific format?

    Regards
    Tuesday, October 27, 2009 5:38 AM
  • ya i need that stored procedure..can u write that
    Tuesday, October 27, 2009 5:44 AM
  •  
       You can easily solve this by using following process:

              1. take the max(isnull(CustomerID,0)) from customer table and assign to a output variable (say CUSTID)
              2. set @CUSTID=@CUSTID+1
              3. you can return this variable @CUSTID


    Lakshman
    Tuesday, October 27, 2009 6:00 AM
  • ok......i am not understanding it .........but u can write fully stored procedure...
    Tuesday, October 27, 2009 6:02 AM
  • create

     

    procedure Proc_Customerid

    @VarCustomerID

    varchar(50) output

    as

     

    select @VarCustomerID=max(isnull(CustomerID,0)) from dbo.Customer

     

    set

     

    @VarCustomerID=@VarCustomerID+1

     

    GO


    Lakshman
    • Proposed as answer by Bharani 3010 Tuesday, October 27, 2009 8:33 AM
    • Marked as answer by Kalman Toth Saturday, October 31, 2009 2:19 PM
    Tuesday, October 27, 2009 6:39 AM
  • while executing  in sql ..how to give ... means for example to check whether is executing

    how to declare?
    EXample if i give cust11 and it should return next cust12...

    DECLARE ..........????????
    Tuesday, October 27, 2009 6:57 AM
  • declare @i as varchar(50)
    exec Proc_Customerid @i output
    print @i
    • Proposed as answer by Bharani 3010 Tuesday, October 27, 2009 8:34 AM
    Tuesday, October 27, 2009 8:25 AM
  • How UserID is defined in your table? Is it an identity field?

    Have you visited the link I provided?
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    Tuesday, October 27, 2009 1:10 PM