Pass Json object of parameters to stored procedure

[bbwfs]

• Goal: I would like to be able to pass parameters to a sql stored procedure through the additional scope in a SQL Resource

• Steps: I've tried passing them as a string

• I've tried passing as an array of strings

• I've tried passing as an object of key value pairs (screenshot below)

• Screenshots: In the screenshot, I would like to be able to pass the parameters as an object of some sort through the additionalScope. In this case, I would like the values in "data" on the highlighted left to become the parameters in the stored procedure on the highlighted right.

Thank you

[bbwfs]

Hi @Paulo , thank you for your time in Office Hours today.

We tried using the GUI setup during Office Hours, but were running into IDENTITY_INSERT issues (which is why I was trying the json/parameters approach with sql instead of GUI) from the original post.

The problem is with jsUpsertContacts and spUpsertContacts. I also created a vTemp variable for debugging jsUpsertContacts if it's helpful.

Here are the table defs and app json export.

TableDefs:

/****** Object: Table [dbo].[Customer] Script Date: 2/6/2025 2:20:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] nvarchar NOT NULL,
[CustomerType] [int] NULL,
CONSTRAINT [PK__Customer__A4AE64B832E4977B] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Contacts] Script Date: 2/6/2025 2:20:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Contacts](
[ContactId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[FirstName] nvarchar NOT NULL,
[LastName] nvarchar NOT NULL,
[PhoneNumber] varchar NULL,
CONSTRAINT [Contacts_pk] PRIMARY KEY CLUSTERED
(
[ContactId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contacts] WITH CHECK ADD CONSTRAINT [Contacts_Customer_CustomerID_fk] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([CustomerID])
GO

ALTER TABLE [dbo].[Contacts] CHECK CONSTRAINT [Contacts_Customer_CustomerID_fk]
GO

APP Json
CustomerDB.json (73.3 KB)

Where we left off in Office Hours was that even though we removed the ContactId field from the targetRow object, it was still trying to pass ContactId on insert.

Thank you for hosting Office Hours and for your time.

Will

[Paulo]

Hi Will! Thank you for adding more screenshots and context. I'm looking into it now.

[Paulo]

Hi @bbwfs, thank you for your patience!

Update:
Upsert doesn't play well with 'IDENTITY'. It seems we always send the null value regardless of whether it's in the changeset.

Full context:

Although we are deleting the primary key (contact_id in your case), the upsert query sends null as its value. Because this key has been defined as an IDENTITY column, the operation fails as Retool tries to set it to null.

I reproduced this issue by creating a "customers" table with the following query:

CREATE TABLE customers (
    id INT IDENTITY(1,1) PRIMARY KEY, 
    name NVARCHAR(100) NOT NULL,
    company NVARCHAR(255) NOT NULL
);

When I try upserting with an id that already exists:

1. Same approach as yours, using a State variable:
   

   

2. MSSQL Resource using additional scope to pass the id and changeset:
   

   

3. Triggered from a JS query:
   

   

The query succeeds, and the table updates with no issues:

However, when I try with an id that does not exist:

I run into the same issue:

We created a feature request to be able to support this data type for the primary key during upserts. Until this is available, a couple of workarounds are:

1. Change the data type for the primary key. But the tradeoff is that someone could manually set the value for the primary key and this may not be something you want to.
2. Use three different queries to handle the insert/update. One that checks if the record exists and, on failure, runs an insert query, while on success, runs the update.

[bbwfs]

Thank you very much for looking at this @Paulo and for the update. Is there a recommended way to watch for this request if/when it goes in in the future?

[Paulo]

Hi @bbwfs, we'll share updates here.

It is not that Retool is sending the key we are using to filter by; it's actually sending null (I updated the post above to prevent confusion).

From the network tab:

Although the k-v pair is not in the changeset, it's trying to set the primary key to null.

As a workaround, we can use a bulk upsert. The following worked for me:

Wrap the target with square brackets in the JS query:

[bbwfs]

Thank you @Paulo,

I tried this out and it does work, but I'm trying to avoid having to create multiple queries per action.

I'm playing around with moving the upsert functionality to the SQL server to limit the number of Resource queries I would have to define for Inserts and Updates, I used a little ChatGPT help to create a stored procedure that takes a json object, table name, and primary key. It dynamically parses out the json and upserts it into the table name based of the primary key. I've been able to use it in several spots by shaping the json in retool to the table. So far it has worked well.

Thank you very much for all your help on this.

Will

[Paulo]

You are welcome!

The bulk upsert approach I shared above should prevent having to create multiple queries.

Please let me know if you have any questions.

[bbwfs]

Thank you @Paulo it sure does. I didn't read it close enough before. Thank you very much.

[Paulo]

You are welcome!

I'll mark the workaround as a solution for now so we can unblock other users, but we'll update it once we have a built-in way to do this with a single record.