Feature Request: Add option to not return updated row after GUI mode update. Previously: Error updating a record on table with computed column

Feature Requests
#1

I am using SQL Server and I have a computed column that uses a function. I am using the GUI query builder to make an update query. This is throwing the following error:

Column ‘inserted.subtotal’ cannot be referenced in the OUTPUT clause because the column definition contains a subquery or references a function that performs user or system data access. A function is assumed by default to perform data access if it is not schemabound. Consider removing the subquery or function from the column definition or removing the column from the OUTPUT clause.

SQL Server cannot return certain types of calculated columns n the OUTPUT clause (MSSQLSERVER_4186) and throws this error if you try.

I do not see a reason to use OUTPUT on an update query. I see that this would be a problem for an INSERT query maybe? But if you requery after doing the insert anyway than I do not see a reason to include the OUTPUT clause.

A proposed solution would be to have a checkbox in the advanced query parameters to exclude the OUTPUT clause entirely. You could also let us list or exclude the fields to include in the OUTPUT clause. A third option would be to only include those columns in the changeset.

I also assume I can just use SQL Mode to get around this (but I like GUI mode!) I’ll try that out.

1 Like
#2

Confirming that using SQL Mode gets around the issue.

#3

Ah I see, so the GUI update does return the updated item in it’s .data property after it runs and that sounds like what is causing the issue here. I think there are a few use-cases that this helps support (though you’re right that INSERTS are the most useful to have the return in). Would you mind sharing the SQL mode query setup you went with to get around this? Perhaps we can move this over to a feature request to add an option to disable the OUTPUT in GUI mode queries!

#4

I just wrote a plain old UPDATE query:

update [dbo].[invoices]  set 
 [order_date] = {{dtInvoiceDate.formattedString}}, 
 [status_id] = {{selStatus.value}}, 
 [terms_id] = {{selTerms.value}}, 
 [po_number] = {{txtPONumber.value}}, 
 [ship_to_company] = {{txtCompany.value}}, 
 [ship_to_name] = {{txtContact.value}}, 
 [ship_to_street] = {{txtStreet.value}}, 
 [ship_to_city] = {{txtCity.value}}, 
 [ship_to_state] = {{selState.value}}, 
 [ship_to_zip] = {{txtZIP.value}}, 
 [ship_to_phone] ={{txtPhone.value}},
 [ship_to_fax] = {{txtFax.value}}, 
 [ship_to_email] = {{txtEmail.value}}, 
 [shipping_charge] = {{txtShipping.value}}, 
 [sales_tax_precent] ={{txtTaxPercent.value}}, 
 [order_instructions] = {{txtOrderInstructions.value}}, 
 [a_r_instructions] = {{txtARInstructions.value}}, 
 [account_notes] = {{txtAccountNotes.value}}, 
 [discount_percent] = {{txtDiscountPercent.value}}, 
 [discount_amount] = {{txtDiscountAmount.value}}, 
 [paid_date] = {{dtPaidDate.formattedString}},  
 [ship_type_id] ={{selShipType.value}} 
 where [invoice_id] = {{tblInvoices.selectedRow.data.invoice_id}}
#5

Awesome, thank you for sharing! I've moved this over to the feature request section to add a checkbox to *not* return the updated row in the data property of GUI mode SQL updates