How do I determine whether a stored procedure exists or not? RRS feed

  • Question

  • User-1044962950 posted
    Is there any way using the Data Access Application block  to determine whether a stored procedure exists or not? If I call GetStoredProcCommand or ExecuteReader or ExecuteDataSet, the method will throw an InvalidOperationException with the message "The stored procedure 'usp_ProcName' doesn't exist", but I don't know if I can count on that message being consistent across multiple database providers.

    The idea here is that I would create the stored procedure on demand if it doesn't exist, and just execute it otherwise.
    Thursday, August 3, 2006 4:31 PM

All replies

  • User-1355649840 posted
    Relying on an exception is probably a bad way to go, anyway. That tends to be a very slow and expensive operation.

    For SQL Server, you would have to query the system tables to see if the proc exists. I imagine Oracle would probably require a similar approach. You may need to be concerned about security if you are adding procs on the fly.
    Thursday, August 3, 2006 11:11 PM
  • User-1044962950 posted

        Thanks for the suggestion. I wasn't thrilled about the prospect of relying on an exception, but it was only a one time penalty, so it would be relatively minor. I'll probably end up implementing a stored procedure to test for the existance of a stored procedure so that I can call it in a database neutral way. If I cache the results of that call, then it should have a relatively low performance impact.
    Friday, August 4, 2006 10:52 AM