We have a MelissaData T-SQL Script that has a license and tracks how many records are cased. We pay by the number of records processed. I want some type of warning to user that if records exceeds 3 million, are you sure this is correct table to process Is there a way in a stored procedure to display a message if record count from a table is >= 3000000 and stop the stored procedure from running? Or display message and ask user to press 'Y' to continue or 'N' to stop?
You could certainly check the rowcount then require the user to rerun the procedure after setting a y/n type flag. Maybe using a parameter like this: create procedure MelissaData @forceThreeMillion bit = 0 as if @forceThreeMillion 1 and (select count(*) from CertainTable) >= 3000000 begin raiserror('Record count exceeds 3,000,000. Are you sure this is the correct table to process? If so, rerun the procedure using @forceThreeMillion = 1', 0, 0) return end ... original body of procedure ...
Doing such a popup via a Stored Procedure is only possible in a very specific scenario: 1. Using SQLCLR to do the Message Box AND: 2. Executing the SQLCLR object in SQL Server Express LocalDB (as it runs as a User process) Outside of that very narrow use-case, this cannot be done in a Stored Procedure (nor would it be a good idea to do so anyway), but it is possible to prompt the user if running a SQL script (though still a bad idea if running within a Transaction). Please see my answer to the following Question, here on Ask.SQLServerCentral, for details on how to do this using SQLCMD mode: [Does anyone have an example of a CLR stored procedure calling a yes/no MessageBox and returning the value to the calling script?] :