search

cmeeren / Facil

Facil generates F# data access source code from SQL queries and stored procedures. Optimized for developer happiness.
MIT License
140 stars 7 forks source link

re: execute rollback when getting column info from stored procedure that also executes updates #23

Closed costa100 closed 2 years ago

costa100 commented 2 years ago

Hi,

I am not too sure this doable, but I will put it out there. I have a stored procedure that updates records based on some condition, it puts the modified records in a temp table using an output clause, then it returns the data from the temp table.

The problem is that when the generator runs it gets the columns information from the stored procedure, but it also executes the stored procedure, and it commits the data. Is it possible to place the operation that gets the columns information in a transaction, get the structure of the resultset, then rollback then entire thing? Otherwise it is a bit annoying because I have to reset that data to undo the operation and to go back to the previous state.

Thank you

costa100 commented 2 years ago

Another way to skin this cat would be to use a stored procedure parameter @isRuntime int = 0 and distinguish between "design time " and "run time", If @isRuntime = 0 then return an empty resultset with the same structure as the real one returned at "run time".

This should work, but it is a bit of pain, especially when the stored procedure is complicated.

cmeeren commented 2 years ago

Using a transaction that is rolled back seems like a good idea.

I assume the procedure is using dynamic SQL?

costa100 commented 2 years ago

No, the stored procedure does not use dynamic SQL.

It simply modifies some rows, then it returns them in a temp table. The problem is that, whatever method the generator uses to figure out the structure of the select statement that gets returned, it actually runs the stored procedure and it makes changes to the data.

cmeeren commented 2 years ago

The problem is that, whatever method the generator uses to figure out the structure of the select statement that gets returned, it actually runs the stored procedure and it makes changes to the data.

Yes, that is a (documented) feature.

Nevermind dynamic SQL. I remember now that in addition to procedures and scripts with dynamic SQL, stored procedures sometimes need this even if they don't contain dynamic SQL.

I'll implement the transaction rollback.

costa100 commented 2 years ago

Thank you!!