Click here to Skip to main content
15,038,345 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i am insert the values in different tables through one store procedure. after insertion of one table (A) i need the primary key values in next one table (B) as a foreign key next table(B) I am working in sql server management studio 2012 . please help me on this scenarios.

C#
ALTER PROCEDURE [dbo].[usp_InsertCustomer_Subscription]
	-- Add the parameters for the stored procedure here
	@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 added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET IDENTITY_INSERT Customer_Subscription_Accounts ON;
    -- Insert statements for procedure here
	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)
	Select @CustomerSubId=@@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)

	SET IDENTITY_INSERT Customer_Subscription_Accounts OFF;
END



when i use this store procedure that through error message

Quote:
Cannot insert the value NULL into column 'Id', table 'AirAdsWellDone.dbo.Customer_Subscription'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'ReceivedBy', table 'AirAdsWellDone.dbo.Customer_Subscription_Accounts'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The statement has been terminated.
Posted
Updated 25-Nov-15 6:15am
v3
Comments
CHill60 25-Nov-15 11:54am
   
What have you tried so far? There is not enough information here for us to help you
Member 11247784 25-Nov-15 12:16pm
   
CHill60 i edit my question please check it
Richard Deeming 25-Nov-15 12:11pm
   
Are you looking for the SCOPE_IDENTITY function[^]?
Member 11247784 25-Nov-15 12:30pm
   
please give me some insertion example in multiple tables using scope_identity
ZurdoDev 25-Nov-15 12:33pm
   
The error is with your first table. You never pass a value for ID and ID cannot be null.

1 solution

Looks like you're close. You just need to remove the two SET IDENTITY_INSERT lines, and replace @@IDENTITY with SCOPE_IDENTITY:
SQL
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[^]
   
Comments
Member 11247784 26-Nov-15 8:35am
   
Thankyou

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900