Stored Procedure

[Leon Shaw]

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]

You could use this SQL code:

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

/john

"Leon Shaw" <vn*****@msn.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...

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]

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" <vn*****@msn.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...

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]

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" <St***@Orr.net> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...

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" <vn*****@msn.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...

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]

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/de...classtopic.asp
http://msdn.microsoft.com/library/de...isualbasic.asp

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

"Leon Shaw" <vn*****@msn.com> wrote in message
news:et**************@TK2MSFTNGP11.phx.gbl...

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" <St***@Orr.net> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...

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" <vn*****@msn.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...

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]

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" <St***@Orr.net> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...

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/de...classtopic.asp http://msdn.microsoft.com/library/de...isualbasic.asp
--
I hope this helps,
Steve C. Orr, MCSD
http://Steve.Orr.net

"Leon Shaw" <vn*****@msn.com> wrote in message
news:et**************@TK2MSFTNGP11.phx.gbl...

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" <St***@Orr.net> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...

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" <vn*****@msn.com> wrote in message
news:Oa**************@TK2MSFTNGP11.phx.gbl...
> 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]

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" <vn*****@msn.com> wrote in message
news:ei**************@TK2MSFTNGP11.phx.gbl...

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" <St***@Orr.net> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...

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/de...classtopic.asp

http://msdn.microsoft.com/library/de...isualbasic.asp

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

"Leon Shaw" <vn*****@msn.com> wrote in message
news:et**************@TK2MSFTNGP11.phx.gbl...

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" <St***@Orr.net> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...
> 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" <vn*****@msn.com> wrote in message
> news:Oa**************@TK2MSFTNGP11.phx.gbl...
> > 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?
> >
> >
>
>