What is the best approach for breaking down stored procedures in the long run?


What is the best approach of breaking long running stored procedures (up to 20 minutes)? Inside the Stored procedure is wrapped in a transaction. If I close connection will this transaction be rolled back? Another approach is to start a transaction in C# before I start the stored procedure and when I want to cancel the stored procedure I just need to rollback the C# transaction.

If you close the connection, SQL Server will rollback the transaction if it notices the disconnect before the transaction commits. There'll be a (very) small time window where the transaction might complete just when you disconnect.

A custom transaction adds complexity and has few benefits for a single stored procedure call. So I'd go for the disconnect.