Passing non-datatable parameter in the table with Datatable parameters using SQL Server-CodePudding

I have only 2 columns/parameters in the Datatable which I am trying to insert in the table through stored procedure.

But I also want to add another parameter which is not in the DataTable.

ALTER PROCEDURE [Job].[prc_A]
@JobId int = 0,
@StartDate datetime,
@EndDate datetime,
@JobPropertyValue as JobPropertyValuesType READONLY

Here is the query:

if(@JobId = 0)
    BEGIN
INSERT INTO TableA (StartDate, EndDate)
VALUES(@StartDate, @EndDate)
SELECT CAST(SCOPE_IDENTITY() as int)

---A New JobId gets created after first insert query. I want to add that JobId in Table B now with DataTable Parameters---
INSERT INTO TableB (JobId, JobPropertyType, JobValue)
    SELECT  __________________, JobPropertyType, JobPropertyTypeName  
    FROM @JobPropertyValue

(using stored procedure)

What's the right way to insert JobId which is present in a different table and other two parameters from DataTable? (I don't want to make any changes to the Code part or Adding the JobId(Fetching it from the Table) in the DataTable directly) I am using Asp.Net Core in to send request.

CodePudding user response：

Assuming TABLEA's IDENTITY column is the JobId, then just

if(@JobId = 0)
BEGIN
    INSERT INTO TableA (StartDate, EndDate)
    VALUES(@StartDate, @EndDate)
    set @jobid  = SCOPE_IDENTITY() 

    ---A New JobId gets created after first insert query. I want to add that JobId in Table B now with DataTable Parameters---
    INSERT INTO TableB (JobId, JobPropertyType, JobValue)
        SELECT  @jobid, JobPropertyType, JobPropertyTypeName  
        FROM @JobPropertyValue