Looks like you're close. You just need to remove the two
SET IDENTITY_INSERT
lines, and replace
@@IDENTITY
with
SCOPE_IDENTITY
:
ALTER PROCEDURE [dbo].[usp_InsertCustomer_Subscription]
@FK_UserId nvarchar(128),
@FK_AdvertiserDetailsId int,
@FK_CityId int,
@FK_CategoryId int,
@FK_AdvertiserId int,
@AddedOn datetime,
@AddedBy nvarchar(128),
@UpdatedOn datetime,
@UpdatedBy nvarchar(128),
@IsActive bit,
@SubscriptionExpiryDate datetime
AS
BEGIN
DECLARE @CustomerSubId int;
SET NOCOUNT ON;
INSERT INTO Customer_Subscription
(
FK_UserId,
FK_AdvertiserDetailsId,
FK_CityId,
FK_CategoryId,
FK_AdvertiserId,
AddedOn,
AddedBy,
UpdatedOn,
UpdatedBy,
IsActive,
SubscriptionExpiryDate
)
VALUES
(
@FK_UserId,
@FK_AdvertiserDetailsId,
@FK_CityId,
@FK_CategoryId,
@FK_AdvertiserId,
@AddedOn,
@AddedBy,
@UpdatedOn,
@UpdatedBy,
@IsActive,
@SubscriptionExpiryDate
);
SET @CustomerSubId = SCOPE_IDENTITY();
INSERT INTO Customer_Subscription_Accounts
(
SubscriptionDate,
FK_Customer_SubscriptionId,
AddedOn,
AddedBy,
UpdatedOn,
UpdatedBy,
IsActive,
SubscriptionExpiryDate
)
VALUES
(
GETDATE(),
@CustomerSubId,
@AddedOn,
@AddedBy,
@UpdatedOn,
@UpdatedBy,
@IsActive,
@SubscriptionExpiryDate
);
END
SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record[
^]