Scope_identity is sql function which is return the last identity values that are generated in any table in the current session. It return value that is inserted into identity columns.
@@IDENTITY function is also use for return idendity but Scope_identity returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
SP for that case –
CREATE procedure proc_InsertShoppcust
(
@insertid int output,
@email varchar(50),
@sessionid nvarchar(50),
@datevalue datetime
)
as
insert into mpcustEmail
(
[email],
[sessionid],
[datevalue]
)
values
(
@email,
@sessionid,
@datevalue
)
select @insertid = scope_identity()
GO
Here I use @insertid as output parameter. Scope_identity return identity value through output parameter in the same scope.
CREATE procedure proc_InsertShoppcust
(
@insertid int output,
@email varchar(50),
@sessionid nvarchar(50),
@datevalue datetime
)
as
insert into mpcustEmail
(
[email],
[sessionid],
[datevalue]
)
values
(
@email,
@sessionid,
@datevalue
)
select @insertid = @@IDENTITY
GO
Suggestions are always welcome.
Thanks.
Thanks.
