Stored Procedure

Discussion in 'ASP .Net' started by Leon Shaw, Jul 19, 2003.

  1. Leon Shaw

    Leon Shaw Guest

    How do I implement a stored procedure to insert a new member in a database
    then return the primary key of that member back to the application to be use
    in another table?
     
    Leon Shaw, Jul 19, 2003
    #1
    1. Advertising

  2. Leon Shaw

    John Knoop Guest

    You could use this SQL code:

    SET NOCOUNT ON;
    BEGIN TRAN;
    INSERT...;
    SELECT @@identity AS newID;
    COMMIT TRAN;
    SET NOCOUNT OFF;

    /john

    "Leon Shaw" <> wrote in message
    news:...
    > How do I implement a stored procedure to insert a new member in a database
    > then return the primary key of that member back to the application to be

    use
    > in another table?
    >
    >
     
    John Knoop, Jul 19, 2003
    #2
    1. Advertising

  3. Have your SQL Server query Select @@Identity after it does the insert.
    Then you can get the value back like this:
    newId = (int)MyCommand.ExecuteScalar();

    --
    I hope this helps,
    Steve C. Orr, MCSD
    http://Steve.Orr.net


    "Leon Shaw" <> wrote in message
    news:...
    > How do I implement a stored procedure to insert a new member in a database
    > then return the primary key of that member back to the application to be

    use
    > in another table?
    >
    >
     
    Steve C. Orr, MCSD, Jul 19, 2003
    #3
  4. Leon Shaw

    Leon Shaw Guest

    Will This Work? And how do I get this MemberId in a variable in code and
    pass it to another table?
    CREATE PROCEDURE Add_Member
    (
    @FirstName varchar(50)
    @LastName varchar(50)
    @etc varchar(50)
    )
    AS
    INSERT INTO Member
    (
    FirstName,
    LastName,
    etc
    )
    VALUES
    (
    @FirstName
    @LastName
    @etc
    )
    SELECT @MemberID = @@IDENTITY

    "Steve C. Orr, MCSD" <> wrote in message
    news:%23K%...
    > Have your SQL Server query Select @@Identity after it does the insert.
    > Then you can get the value back like this:
    > newId = (int)MyCommand.ExecuteScalar();
    >
    > --
    > I hope this helps,
    > Steve C. Orr, MCSD
    > http://Steve.Orr.net
    >
    >
    > "Leon Shaw" <> wrote in message
    > news:...
    > > How do I implement a stored procedure to insert a new member in a

    database
    > > then return the primary key of that member back to the application to be

    > use
    > > in another table?
    > >
    > >

    >
    >
     
    Leon Shaw, Jul 19, 2003
    #4
  5. No, don't select the identity value into a private stored procedure variable
    or you'll never be able to get to it.
    Instead the final line should look like this:
    SELECT @@IDENTITY

    Alternately you could define @MemberID as an output parameter for your
    sproc. Then use ADO.NET parameter objects. After you execute the query,
    check that parameter and it should be filled with the identity value.
    Here's more info:
    http://msdn.microsoft.com/library/d...systemdatasqlclientsqlparameterclasstopic.asp
    http://msdn.microsoft.com/library/d...ngparameterizedstoredprocedurevisualbasic.asp

    --
    I hope this helps,
    Steve C. Orr, MCSD
    http://Steve.Orr.net



    "Leon Shaw" <> wrote in message
    news:...
    > Will This Work? And how do I get this MemberId in a variable in code and
    > pass it to another table?
    > CREATE PROCEDURE Add_Member
    > (
    > @FirstName varchar(50)
    > @LastName varchar(50)
    > @etc varchar(50)
    > )
    > AS
    > INSERT INTO Member
    > (
    > FirstName,
    > LastName,
    > etc
    > )
    > VALUES
    > (
    > @FirstName
    > @LastName
    > @etc
    > )
    > SELECT @MemberID = @@IDENTITY
    >
    > "Steve C. Orr, MCSD" <> wrote in message
    > news:%23K%...
    > > Have your SQL Server query Select @@Identity after it does the insert.
    > > Then you can get the value back like this:
    > > newId = (int)MyCommand.ExecuteScalar();
    > >
    > > --
    > > I hope this helps,
    > > Steve C. Orr, MCSD
    > > http://Steve.Orr.net
    > >
    > >
    > > "Leon Shaw" <> wrote in message
    > > news:...
    > > > How do I implement a stored procedure to insert a new member in a

    > database
    > > > then return the primary key of that member back to the application to

    be
    > > use
    > > > in another table?
    > > >
    > > >

    > >
    > >

    >
    >
     
    Steve C. Orr, MCSD, Jul 19, 2003
    #5
  6. Leon Shaw

    Leon Shaw Guest

    So how do I get that identity column inside a variable to be pass to another
    table that reference that member?
    "Steve C. Orr, MCSD" <> wrote in message
    news:%23D6%...
    > No, don't select the identity value into a private stored procedure

    variable
    > or you'll never be able to get to it.
    > Instead the final line should look like this:
    > SELECT @@IDENTITY
    >
    > Alternately you could define @MemberID as an output parameter for your
    > sproc. Then use ADO.NET parameter objects. After you execute the query,
    > check that parameter and it should be filled with the identity value.
    > Here's more info:
    >

    http://msdn.microsoft.com/library/d...systemdatasqlclientsqlparameterclasstopic.asp
    >

    http://msdn.microsoft.com/library/d...ngparameterizedstoredprocedurevisualbasic.asp
    >
    > --
    > I hope this helps,
    > Steve C. Orr, MCSD
    > http://Steve.Orr.net
    >
    >
    >
    > "Leon Shaw" <> wrote in message
    > news:...
    > > Will This Work? And how do I get this MemberId in a variable in code and
    > > pass it to another table?
    > > CREATE PROCEDURE Add_Member
    > > (
    > > @FirstName varchar(50)
    > > @LastName varchar(50)
    > > @etc varchar(50)
    > > )
    > > AS
    > > INSERT INTO Member
    > > (
    > > FirstName,
    > > LastName,
    > > etc
    > > )
    > > VALUES
    > > (
    > > @FirstName
    > > @LastName
    > > @etc
    > > )
    > > SELECT @MemberID = @@IDENTITY
    > >
    > > "Steve C. Orr, MCSD" <> wrote in message
    > > news:%23K%...
    > > > Have your SQL Server query Select @@Identity after it does the insert.
    > > > Then you can get the value back like this:
    > > > newId = (int)MyCommand.ExecuteScalar();
    > > >
    > > > --
    > > > I hope this helps,
    > > > Steve C. Orr, MCSD
    > > > http://Steve.Orr.net
    > > >
    > > >
    > > > "Leon Shaw" <> wrote in message
    > > > news:...
    > > > > How do I implement a stored procedure to insert a new member in a

    > > database
    > > > > then return the primary key of that member back to the application

    to
    > be
    > > > use
    > > > > in another table?
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Leon Shaw, Jul 19, 2003
    #6
  7. In the example I gave the identity of the record you just inserted will be
    in the newId variable. Then you can pass it wherever you want, perhaps
    putting it into a SQLParameter object to call some other query.

    --
    I hope this helps,
    Steve C. Orr, MCSD
    http://Steve.Orr.net


    "Leon Shaw" <> wrote in message
    news:...
    > So how do I get that identity column inside a variable to be pass to

    another
    > table that reference that member?
    > "Steve C. Orr, MCSD" <> wrote in message
    > news:%23D6%...
    > > No, don't select the identity value into a private stored procedure

    > variable
    > > or you'll never be able to get to it.
    > > Instead the final line should look like this:
    > > SELECT @@IDENTITY
    > >
    > > Alternately you could define @MemberID as an output parameter for your
    > > sproc. Then use ADO.NET parameter objects. After you execute the

    query,
    > > check that parameter and it should be filled with the identity value.
    > > Here's more info:
    > >

    >

    http://msdn.microsoft.com/library/d...systemdatasqlclientsqlparameterclasstopic.asp
    > >

    >

    http://msdn.microsoft.com/library/d...ngparameterizedstoredprocedurevisualbasic.asp
    > >
    > > --
    > > I hope this helps,
    > > Steve C. Orr, MCSD
    > > http://Steve.Orr.net
    > >
    > >
    > >
    > > "Leon Shaw" <> wrote in message
    > > news:...
    > > > Will This Work? And how do I get this MemberId in a variable in code

    and
    > > > pass it to another table?
    > > > CREATE PROCEDURE Add_Member
    > > > (
    > > > @FirstName varchar(50)
    > > > @LastName varchar(50)
    > > > @etc varchar(50)
    > > > )
    > > > AS
    > > > INSERT INTO Member
    > > > (
    > > > FirstName,
    > > > LastName,
    > > > etc
    > > > )
    > > > VALUES
    > > > (
    > > > @FirstName
    > > > @LastName
    > > > @etc
    > > > )
    > > > SELECT @MemberID = @@IDENTITY
    > > >
    > > > "Steve C. Orr, MCSD" <> wrote in message
    > > > news:%23K%...
    > > > > Have your SQL Server query Select @@Identity after it does the

    insert.
    > > > > Then you can get the value back like this:
    > > > > newId = (int)MyCommand.ExecuteScalar();
    > > > >
    > > > > --
    > > > > I hope this helps,
    > > > > Steve C. Orr, MCSD
    > > > > http://Steve.Orr.net
    > > > >
    > > > >
    > > > > "Leon Shaw" <> wrote in message
    > > > > news:...
    > > > > > How do I implement a stored procedure to insert a new member in a
    > > > database
    > > > > > then return the primary key of that member back to the application

    > to
    > > be
    > > > > use
    > > > > > in another table?
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Steve C. Orr, MCSD, Jul 20, 2003
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sarmad Aljazrawi

    New Stored Procedure Template in .Net

    Sarmad Aljazrawi, Dec 16, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    541
    Sarmad Aljazrawi
    Dec 16, 2003
  2. ES
    Replies:
    2
    Views:
    1,536
    jgy3183
    Aug 20, 2004
  3. Patrick
    Replies:
    2
    Views:
    5,958
    Gopinath S
    Feb 15, 2005
  4. Mike P
    Replies:
    0
    Views:
    3,316
    Mike P
    Jun 19, 2006
  5. AlexWare
    Replies:
    2
    Views:
    767
    Paul Uiterlinden
    Oct 23, 2009
Loading...

Share This Page