Stored Procedure

  • Thread starter Thread starter Leon Shaw
  • Start date Start date
L

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?
 
You could use this SQL code:

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

/john
 
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();
 
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
 
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
 
So how do I get that identity column inside a variable to be pass to another
table that reference that member?
 
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.
 
Back
Top