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

[costa100]

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]

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]

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

I assume the procedure is using dynamic SQL?

[costa100]

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]

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]

Thank you!!