Pass Variable declared in SQL to another SQL Query

[nroeder]

Can you pass a variable declared in a sql query to another sql query?

[ScottR]

Can you share some more information?
What’s the user scenario?

[nroeder]

Customer, Properties, Contacts - tables.

I have a sql query that inserts a customer record, uses the CustomerId generated and creates a property for that customer.

I need to insert [] of contacts for that customer into the contacts table, but need the CustomerId that was generated. I've tried a while loop in my sql statement but can't see to get it to work. I thought I might be able to pass the @CustomerId into a secondary sql query that's triggered upon success.

DECLARE @Output As TABLE (CustomerId INT)
INSERT INTO
  Customer (
    FirstName,
    LastName,
    Company,
    BillingAddress1,
    BillingCity,
    BillingStateId,
    BillingZipCode,
    PhoneNumber,
    Email,
    CustomerNotes,
    LocationId,
    LeadSourceId,
    ReferredByCustId,
    SalespersonUserId,
    IsActive,
    CreatedDate,
    UpdatedDate,
    ArchivedBy,
    Photo,
    DefaultColor
  )
  OUTPUT INSERTED.CustomerId INTO @Output (CustomerId)
VALUES
  (
    'Large',
    'Project',
    {{tableLeads.selectedRow.data.Company}},
    {{tableLeads.selectedRow.data.Address1}},
    {{tableLeads.selectedRow.data.City}},
    {{tableLeads.selectedRow.data.StateId}},
    {{tableLeads.selectedRow.data.Zipcode}},
    '000-000-0000',
    'Large@project.com',
    {{tableLeads.selectedRow.data.Notes}},
    {{selectLeadConvertLocations.value}},
    8,
    -1,
    {{selectLeadConvertTM.value}},
    'True',
    GETUTCDATE(),
    GETUTCDATE(),
    -1,
    'house-stock2.jpg',
    '#C1CA5E'
  )

DECLARE @CustomerId INT
SELECT @CustomerId = CustomerId FROM @OUTPUT

  INSERT INTO 
    Property (
    CustomerId,
    Address1,
    City,
    StateId,
    ZipCode,
    IsActive,
    CreatedDate,
    UpdatedDate,
    ArchivedBy,
    ReceiveHailWarnings,
    HasInspectionReport,
    JobStatusId
    )
  VALUES
  (
    @CustomerId,
    {{tableLeads.selectedRow.data.Address1}},
    {{tableLeads.selectedRow.data.City}},
    {{tableLeads.selectedRow.data.StateId}},
    {{tableLeads.selectedRow.data.Zipcode}},
    'True',
    GETUTCDATE(),
    GETUTCDATE(),
    -1,
    'False',
    'False',
    -1
    )
      
DECLARE @Contacts INT;
DECLARE @i INT;
SET @i = 0;
SET @Contacts = COUNT({{tableContacts.data.LeadLargeProjectContactId}})


WHILE @Contacts > 0
BEGIN
Insert INTO
contacts (CustomerId, FirstName, LastName, Phone, Email, Title, RelationshipId, CreatedDate)
Values (@CustomerId, {{tableContacts.data.FirstName[@i]}}, {{tableContacts.data.LastName[@i]}}, {{tableContacts.data.Phone[@i]}}, {{tableContacts.data.Email[@i]}}, {{tableContacts.data.Title[@i]}}, 2, GETDATE())
SET @Contacts = @Contacts -1;
Set @i = @i + 1; 
END

   
Update leadLargeProject
SET Converted = 1
Where LeadLargeProjectId = {{tableLeads.selectedRow.data.LeadLargeProjectId}} */

[ScottR]

Try using RETURNING and then once retrieved pass that into the next insert for contacts insert

INSERT INTO
  Customer (
    FirstName,
    LastName,
    Company,
    BillingAddress1,
    BillingCity,
    BillingStateId,
    BillingZipCode,
    PhoneNumber,
    Email,
    CustomerNotes,
    LocationId,
    LeadSourceId,
    ReferredByCustId,
    SalespersonUserId,
    IsActive,
    CreatedDate,
    UpdatedDate,
    ArchivedBy,
    Photo,
    DefaultColor
  ) RETURNING CustomerId;

[nroeder]

the declare works fine for setting the variable within the sql statment. But how do i get the variable CustomerId passed to a new sql query?

[ScottR]

You would get the customerID from the first query
{{query1.data.CustomerId}} just make sure you have the first query run successfully and then use the Event handler to trigger the second query.

INSERT INTO
Property (
CustomerId,
Address1,
City,
StateId,
ZipCode,
IsActive,
CreatedDate,
UpdatedDate,
ArchivedBy,
ReceiveHailWarnings,
HasInspectionReport,
JobStatusId
)
VALUES
(
{{query1.data.CustomerId}}
{{tableLeads.selectedRow.data.Address1}},
{{tableLeads.selectedRow.data.City}},
{{tableLeads.selectedRow.data.StateId}},
{{tableLeads.selectedRow.data.Zipcode}}, .....

You could also write a js query that calls both one right after the other but this essentially the same thing when using the Event handler when the first query succeeds.

[Ana]

hi, do you know how to do exact the same thing in bulk insert like on the screenshot? I also need to pass the ID betwen sql queries.

[nroeder]

The only way I've done bulk insert is to use additional scope and loop through all items using JavaScript. If you handle the loop this way then you can use

insertQuery

OUTPUT inserted.UID

Then in subsequent query you would use insertQuery.data.UID['0']