CODEWITHSUNDEEP
asp.netsql-serverstored-proceduressql-server-2014
user4221591
user4221591

Reputation: 2150

Insert data from a datatable and declared variable into a table using a stored procedure

I am trying to insert asp.net grid data to database table [DBO].[tbl_family_member]. I've created a type typ_fam_mem for this. But the problem is the column family_member_id value is generated from another table [DBO].[TBL_FAMILY_HEAD]. Please look at the procedure.

How can I insert data to the table from a datatable and procedure variables?

CREATE TABLE [DBO].[tbl_family_member] 
(
    family_member_id bigint identity(1,1) not null,
    family_head_id bigint NOT NULL,
    mem_name nvarchar(200) not null,
    mem_gender tinyint not null,
    mem_occupation nvarchar(50),
    mem_maritial_status tinyint,
    STATUS INT NOT NULL DEFAULT '1',

    CONSTRAINT pk_family_member PRIMARY KEY (family_member_id)
)

create type typ_fam_mem as table
(   
    mem_name nvarchar(200) not null,
    mem_gender tinyint not null,
    mem_occupation nvarchar(50),
    mem_maritial_status tinyint
)

Stored procedure 

CREATE PROCEDURE [dbo].[P_SET_PROFILE_REGISTRATION] 
    (
     --- FOR FAMILY HEAD TABLE
     @P_NAME NVARCHAR(200),
     @P_GENDER TINYINT,
     ---- FOR FAMILY MEMBER
     @P_FAMILY_DT DBO.typ_fam_mem READONLY,
     @V_OUT TINYINT OUTPUT
    )
AS
    DECLARE @FAMILY_HEAD_ID BIGINT;
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO [DBO].[TBL_FAMILY_HEAD] ([NAME], [GENDER])
        VALUES (@P_NAME, @P_GENDER)

        SET @FAMILY_HEAD_ID = SCOPE_IDENTITY();

        IF @@ROWCOUNT > 0
        BEGIN
            --- block for inserting data into [DBO].[tbl_family_member]
        END
    END TRY
    BEGIN CATCH
    END CATCH
END

Upvotes: 1

Views: 496

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

You can use INSERT...SELECT, specifying the variable with the generated value. The example below also adds a transaction management (appropriate for multi-table inserts unless client app manages the transaction) and error handling. 

CREATE PROCEDURE [dbo].[P_SET_PROFILE_REGISTRATION] 
     --- FOR FAMILY HEAD TABLE
     @P_NAME NVARCHAR(200),
     @P_GENDER TINYINT,
     ---- FOR FAMILY MEMBER
     @P_FAMILY_DT DBO.typ_fam_mem READONLY,
     @V_OUT TINYINT OUTPUT
AS
DECLARE @FAMILY_HEAD_ID BIGINT;
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRAN;

    INSERT INTO [DBO].[TBL_FAMILY_HEAD] ([NAME], [GENDER])
    VALUES (@P_NAME, @P_GENDER)

    SET @FAMILY_HEAD_ID = SCOPE_IDENTITY();

    INSERT INTO dbo.tbl_family_member(
              mem_name
            , mem_gender
            , mem_occupation
            , mem_maritial_status
            , family_head_id
        )
    SELECT 
              mem_name
            , mem_gender
            , mem_occupation
            , mem_maritial_status
            , @FAMILY_HEAD_ID
    FROM @P_FAMILY_DT;

    COMMIT;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO

Upvotes: 1

PSK
PSK

Reputation: 17943

@P_FAMILY_DT is a table type, you can query the table type like a normal table. Your insert should look like 

INSERT INTO [DBO].[tbl_family_member] 
            (family_head_id, 
             mem_name, 
             mem_gender, 
             mem_occupationm, 
             mem_maritial_status) 
SELECT @FAMILY_HEAD_ID, 
       mem_name, 
       mem_gender, 
       mem_occupation, 
       mem_maritial_status 
FROM   @P_FAMILY_DT

Upvotes: 1

Related Questions