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

 

[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();

 

[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]

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

 

[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]

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.