Output parameters in a Stored Procedure

[Guest_imported]

I need some help in retrieving the value of an identity column which is the primary key in a table after inserting a row in that specific table.
The table in question is STUDENT which contains the following columns: STUDENTID, FNAME, MINITIAL, LNAME.

I wrote a stored procedure called sp_addrecord:

create procedure sp_addrecord
@FNAME varchar(30),
@MINITIAL char(1),
@LNAME varchar(30),
as
insert STUDENT (FNAME, MINITIAL, LNAME)
values (@FNAME, @MINITIAL, @LNAME)
return

This stored procedure adds the data to the table, but I need it to return the STUDENTID value as an output so that I can use it later. The STUDENTID datatype is int and its an identity column.

The backend is SQL 7.0 and the front end is ASP. The code on the front end looks sumthin like this...

<%
sql = &quot;sp_getstudentid1 '&quot; & request.form(&quot;FNAME&quot & &quot;', '&quot; & request.form(&quot;MINITIAL&quot & &quot;', '&quot; & request.form(&quot;LNAME&quot & &quot;'&quot;
Set rs1 = conn.Execute(sql)
%>

Any help will be much appreciated. Thanks!

 

[balves]

create procedure sp_addrecord
@FNAME varchar(30),
@MINITIAL char(1),
@LNAME varchar(30),
@NEW_ID int OUTPUT)
as
BEGIN TRANSACTION

insert STUDENT (FNAME, MINITIAL, LNAME)
values (@FNAME, @MINITIAL, @LNAME)

SELECT @NEW_ID = Max(STUDENTID) FROM STUDENT

COMMIT TRANSACTION

return

Of course, you could some error checking code as well. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[Guest_imported]

Thanks much, it works