Is there a way to execute a SQL Server stored procedure with arcpy or Python and retrieve the results returned by the stored procedure?
There is a very similar question posted on ArcGIS forum with no answer so far.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
For working specifically with SQL Server, arguably the best Python package is
pymssql which is:
A simple database interface for Python that builds on top of FreeTDS to provide a Python DB-API (PEP-249) interface to Microsoft SQL Server.
I have used it in production for many years making a ton of SQL SP calls and it worked really, really well.
It is a Python 2.x and 3.x module that allows you to use ODBC to connect to almost any database from Windows, Linux, OS/X, and more. It implements the Python Database API Specification v2.0, but additional features have been added to simplify database programming even more.
Pyodbc is licensed using an MIT license, so it is free for commercial and personal use. You can even use the source code in your own projects. Installers for Windows are available here and most Linux distributions are starting to provide pre-compiled packages. Full source code is also available.
If you will ever need to execute SQL commands for an ArcSDE geodatabase, consider using the ArcSDESQLExecute class which provides a means of executing SQL statements via an ArcSDE connection. I am not sure though if you can shoot SQL commands for stored procedures, but it might be worth trying.