>Is there a way to have a script to display a dialog asking for a confirmation before proceeding? I want to make sure the user who executes the script confirms OK, based on the text of the dialog, before proceeding. Thus, it would then allow the cancel the script as well.

Not in the transact-SQL, but you may try something from the client application or a batch statement.