Pass Variable declared in SQL to another SQL Query

App Building
#1

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

#2

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

#3

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}} */
#4

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;
#5

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?

#6

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.