in SQL Server, scope_identity (), @ identity, and ident_current () can be used to obtain the value of the last inserted record. The difference is:
scope_identity () returns the last identity value inserted into the identity column in the same scope. A scope is a module-stored procedure, trigger, function, or batch processing. Therefore, if the two statements are in the same stored procedure, function, or batch processing, they are in the same scope.
@ identity returns the last id value generated in all tables of the current session
ident_current () returns the final generated id value for any session and specified table in any scope.
Create procedure [DBO]. [prinsertcustomarea]
@ areaname nvarchar (50),
@ userid int,
@ corpid int,
@ areaweblatlon varchar (4000 ),
@ remark nvarchar (50),
@ centerweblatlon nvarchar (50),
@ centerlatlon nvarchar (50),
@ imageurl nvarchar (100 ),
@ areawidth float,
@ areahigh float,
@ acreage float,
@ minlat numeric (9, 6 ),
@ minlon numeric (9, 6),
@ maxlat numeric (9, 6),
@ maxlon numeric (9, 6 ),
@ arealatlon varchar (5000),
@ o_return int output -- declare out parameter
As
Begin
Declare @ ID int;
-- Set nocount on added to prevent extra result sets from
-- Interfering with select statements.
Set nocount on;
Insert into customarea (areaname, userid, corpid, arealatlon, areaweblatlon, remark,
Centerweblatlon, centerlatlon, imageurl, areawidth, areahigh, acreage)
Values (@ areaname, @ userid, @ corpid, '', @ areaweblatlon, @ remark,
@ Centerweblatlon, @ centerlatlon, @ imageurl, @ areawidth, @ areahigh, @ acreage)
Select @ ID = ident_current ('marmarea '); -- the table name must be passed.
Insert into customarealatlon (minlat, minlon, maxlat, maxlon, inputtime, areaid, arealatlon)
Values (@ minlat, @ minlon, @ maxlat, @ maxlon, getdate (), @ ID, @ arealatlon );
Set @ o_return = @ error -- assign a value to the out Parameter
-- @ Error indicates the error returned when the stored procedure is executed.Code, 0 indicates success. Otherwise, an error code is returned;
End