Using pymssql, how to call stored procedure with output

Question

This code works but not sure how to get the output?

storedProcedure = "dbo.myproc"
cursor = conn.cursor()
query = """DECLARE @test_suite_dispatch_id int;
           exec {sp} @test_suite_id={id},
           @test_suite_dispatch_id = @test_suite_dispatch_id OUTPUT
        """.format( sp=storedProcedure, id=TestSuiteData['TestSuite_ID'])
print( query )
cursor.execute( query )
cursor.close()

How do I get the above to work using pymssql's callproc method?

This code does not work:

out = None
cursor.callproc(storedProcedure, 
                (TestSuiteData['TestSuite_ID'], out))

This doesn't work either:

cursor.callproc(storedProcedure, 
                [(TestSuiteData['TestSuite_ID']), out])

I've also tried:

cursor.callproc(storedProcedure, 
                [(TestSuiteData['TestSuite_ID']), pymssql.output(int)])

and

cursor.callproc(storedProcedure, 
                [(TestSuiteData['TestSuite_ID']), pymssql.output(long)])

What do you guys think?

Source: https://stackoverflow.com/a/192032/2965993

I'm trying to figure out the same thing... I'm stuck on this: https://github.com/pymssql/pymssql/blob/98fce86951945e63ee365af4e57336856af681c4/tests/test_sprocs.py#L76-L91 — double_j, Nov 17 '15 at 23:03

score 3 · Answer 1 · answered Nov 18 '15 at 01:02

Okay I figured it out.

Depending on what your output is going to be, here's what I did and it works:

msg = cursor.callproc(store_proc, (file_name, '0x0a', pymssql.output(str)))
print(msg[2])

Obviously my stored proc accepts different values, but you can run with it.

score 1 · Answer 2 · answered Nov 30 '20 at 16:22

First of all, you have to import pymssql.output

from pymssql import output

Then, create a new variable which will be passed as the output parameter to the actual stored procedure (at this point you have to know which type your output parameter will have, check the stored procedure code if you don't know it):

counter = output(int)

All you have to do now is to pass it to the callproc. In the next example, my stored procedure is called sp_test and it expects 4 parameters (the last parameters is an output parameter):

new_value = self.cursor.callproc('sp_test', (5, 0, 0, counter))
return new_value[3]

The result from the sp_test is an array which has the 4 parameters passed to the stored procedure and the last one gets the value from it:

[5, 0, 0, 1463]

Since array indexes start at 0, you get the returned value from the stored procedure in new_value[3].

Using pymssql, how to call stored procedure with output

2 Answers2