For SQL Server 2000, it provides two new functions (Ident_current, Scope_Identity) and improved @@ identity. When you insert a new record, you can call the function:
Print Ident_Current ('Table') 'This will get a new Identity value, regardless of a record in the database (this avoids @@ identity connection restrictions)
Or: Print Scope_identity () This will get the latest recorded Identity value created in other programs such as the current stored procedure, trigger, and more.
There is a problem with the global variable @@ identity, when INSERT is executed, if the table has a trigger program, then inserts a record in another table, then the @@ identity value is the second The Identity value of the tab.
If you are not SQL Server 2000, you'd better have a simple stored procedure to solve this problem.
Create Procedure MyProc
@ Param1 Int
AS
Begin
Set nocount on
INSERT INTO SOMETABLE
(
Intfield
)
Values
(
@ param1
)
Set nocount off
SELECT newid = @@ iDENTITY
End
You can do this in ASP:
<%
FakeValue = 5
Set conn = server.createObject ("adoDb.connection")
Conn.open "
SET RS = conn.execute ("EXEC MyProc @ param1 =" & fakevalue)
Response.write "new id was" & rs (0)
rs.close: set = Nothing
Conn.close: set conn = Nothing
%>
