ermissions are restricted in most environments. Even in development environments, only members of the sysadmin and serveradmin fixed server roles can execute the
extended procedures to grant use of SQL Server's debugger and custom error messages, leaving the developer to their own devices for error handling during development.
Complicating matters is the fact that error trapping is primitive in SQL Server development because some errors can break procedures prematurely. This means you can't find out what's wrong because the call to the error trap in your code never gets executed! So if you are running in Query Analyzer, an error can occur and you may never know what caused it because your procedure stopped before it could be reported. Errors are sent to the client that called them, which leaves Query Analyzer out because it doesn't qualify as a proper client.
I confronted these limitations during a recent project. I had to build functionality into the code to determine what was going wrong with a stored procedure under development.
How can I test my stored procedure code for the different combinations of input parameters so that it will behave as expected? How do I work around the absence of permissions to execute SQL Server Debugger or the ability to use Raiserror to debug my code?
You have two alternatives. One is to create a stored procedure that executes the procedure under development. It will call the procedure with every combination of input parameters possible. The second is to use the command window as a client, and call the procedures from there. This re-routes the server's error messages to the command line, where you can trap them to a file and read them outside Query Analyzer.