I have a python script that uses pyodbc to call an MSSQL stored procedure, like so:

cursor.execute("exec MyProcedure @param1 = '" + myparam + "'")

I call this stored procedure inside a loop, and I notice that sometimes, the procedure gets called again before it was finished executing the last time. I know this because if I add the line

time.sleep(1)

after the execute line, everything works fine.

Is there a more elegant and less time-costly way to say, "sleep until the exec is finished"?

Update (Divij's solution): This code is currently not working for me:

from tornado import gen
import pyodbc

@gen.engine
def func(*args, **kwargs):
    # connect to db
    cnxn_str = """
    Driver={SQL Server Native Client 11.0};
    Server=172.16.111.235\SQLEXPRESS;
    Database=CellTestData2;
    UID=sa;
    PWD=Welcome!;
    """
    cnxn = pyodbc.connect(cnxn_str)
    cnxn.autocommit = True
    cursor = cnxn.cursor()
    for _ in range(5):
        yield gen.Task(cursor.execute, 'exec longtest')

    return

func()

Here's my workaround:

In the database, I make a table called RunningStatus with just one field, status, which is a bit, and just one row, initially set to 0.

At the beginning of my stored procedure, I execute the line

update RunningStatus set status = 1;

And at the end of the stored procedure,

update RunningStatus set status = 0;

In my Python script, I open a new connection and cursor to the same database. After my execute line, I simply add

while 1:
    q = status_check_cursor.execute('select status from RunningStatus').fetchone()
    if q[0] == 0:
        break

You need to make a new connection and cursor, because any calls from the old connection will interrupt the stored procedure and potentially cause status to never go back to 0.

It's a little janky but it's working great for me!