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!

 

[rac2]

You could add a SELECT statement to the stored procedure to obtain the maximum value of the identity column. Then call the stored procedure with a recordset open method.

First, add this SQL as the last statement in sp_addrecord

SELECT MAX(studentid) AS &quot;student_id&quot; FROM student


Then in the ASP script call the stored procedure like this

var cmdAddStudent =  Server.createObject("ADODB.Command");
cmdAddStudent.CommandText = "{CALL sp_addrecord('Bill', 'A', 'Bong')}"

var rsGetNewStudentID = Server.createObject("ADODB.Recordset");

rsGetNewStudentID.open(cmdAddStudent.execute());

var studentID = rsGetNewStudentID("student_id");

Richard

 

[Guest_imported]

Thanks much, it works

 

[gspro]

Also, you can use @@identity in your stored procedure to return the identity of the newly added record.