dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: stored procedure

Hybrid View

  1. #1
    Join Date
    Sep 2002
    Location
    India (Delhi)
    Posts
    199

    Thumbs down stored procedure

    Hi

    i need to insert a record from my asp.net frontend using stored procedure in Sql server database.
    there is a table called Customer
    fields are customerID,CustomerName
    now through stored procedure i need to insert customername and customerID is must be autogenerated
    like
    CustomerID CustomerName
    1 ABC
    2 DEF
    3 XYZ


    is there any way to do this
    iwas trying to do like this
    Code:
    create proc trest
    (@fname varchar(50)="x",
    @x int)
    
    as
    select max(id) as nid from register
    set x=nid+1
    insert into login (id,firstname)values(@x,@fname)
    Keep Posting in this Forum

  2. #2
    Join Date
    Feb 2005
    Location
    "The Capital"
    Posts
    5,306

    Re: stored procedure

    Make the customer ID column an identity column. And now you would not need to worry about this ID and just need to insert the records with customer name. Here is a link that explains the stuff - Understanding the Identity columns. And to get the last value that the identity column got you just do a @@IDENTITY and you get the value. No need to do a select max(..).... Hope this helps. Regards.

  3. #3
    Join Date
    May 2002
    Location
    Colombo,Sri Lanka
    Posts
    1,110

    Re: stored procedure

    Or else

    You can use a trigger to update the second table from inseted table

  4. #4
    Join Date
    Feb 2005
    Location
    "The Capital"
    Posts
    5,306

    Re: stored procedure

    Quote Originally Posted by dinesh123
    Or else

    You can use a trigger to update the second table from inseted table
    What do you mean? The OP only talks of one table. Regards.

  5. #5
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: stored procedure

    ..And to get the last value that the identity column got you just do a @@IDENTITY and you get the value.
    or use SCOPE_IDENTITY()

    from BOL
    Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  6. #6
    Join Date
    Sep 2002
    Location
    India (Delhi)
    Posts
    199

    Thumbs up Re: stored procedure

    Thanx alot all of you for the reply's.
    Keep Posting in this Forum

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)