Stored Procedure Asking for Input While Processing

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.

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.

StackExchange Database Administrators Q#213693, answer score: 9