4

So I am asking this to see if this is something that is even possible within SQL.

I am currently writing up some stored procedures for a VB.Net application I am working on to use to interact with the data within my database.

I am creating a procedure to delete a set of records, but want to know if it's possible for the Procedure itself to sort of ask for confirmation that the item is to be deleted. Something like a message prompt or something where the procedure pauses to wait for further input.

If this isn't doable, I know how I can manage it on the VB.Net side, I am just trying to keep as much of the SQL and database stuff in the database itself as possible.

  • Even were this possible, how would this be displayed/round tripped to the VB.net client? The SP would have to hold locks on whatever it had done up to that point until the client got around to responding. – Paddy Aug 1 '18 at 13:29
9

Short answer is that a stored procedure cannot prompt to accept an input to utilize in the transaction after it has been executed.

What you could do is use a stored procedure to obtain a count or the value you are looking to validate. Based on this, you can use a logic function in your code to then call the second stored procedure which performs the action.

Likewise this IF ELSE logic could be incorporated into the procedure. Let's say you want to delete all records from a table where the value = 1, you expect 50 deletes. You could call the stored procedure and input the value 50 and it would run:

SELECT COUNT(*) FROM TABLE WHERE Value = 1 

You can do validation to say that count was greater, less, or equal to 50, and then it can either call another procedure and pass another parameter to it which could be a delete procedure, or it could just end the action.

I'm typically against daisy chaining stored procedures and I like to keep the business logic in the application though, so I would suggest handling it in VB.

7

The closest you can reasonably come is for the stored procedure to require a transaction. eg

if @@trancount = 0 throw 50001, 'A transaction is required to run this procedure', 10

Then the application will have to explicitly commit the changes after running the stored procedure.

1

There are a couple of "standard" ways to handle this - each better in varying situations.

  1. Add a @Force parameter. The first time the procedure is executed, RAISERROR and display the message box to the user. If the user assents, call the procedure again with the @Force parameter set. If confirmation for changes missed due to TOCTOU issues are a problem, make the @Force parameter a list of records which were approved.
  2. Break the procedure into smaller parts so that the application is exposed into the intermediate state. (BuildChangeSet then IF EXISTS(SELECT 1 FROM #ChangesPending) → message box then ExecuteChanges)
  3. Implement the procedure in C#/VB using a ORM to have SQL-like ease of accessing tables, while avoiding "actual" stored procedures
  4. Make your app avoid indelible changes, prefer obvious "undo" buttons over confirmations

There are also a number of solutions which (while technically possible) will result in a DBA eventually wishing for your demise:

  1. Open a transaction, make your update, return a warning via a result set or a non-terminating error, then hold the transaction uncommitted until the user gives consent or requests that the transaction be rolled back. (Locks may be held for minutes until the user responds)
  2. Use a communication mechanism to call back from the sproc while waiting. (Service Broker does permit this, as does CLR when run with Unsafe, COM interop, external scripts, and I'm sure a few other ways)

Even if you did solve this, it still limits you to a single-tier app since now you have UI intermixed in your data access.


TL;DR: Yes, it is possible to wait on an outside event from a stored procedure, but please don't – that is not what SQL Server is for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.