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: github.com/pymssql/pymssql/blob/… — double_j
– double_j, Commented Nov 17, 2015 at 23:03

double_j · Accepted Answer · 2015-11-18 01:02:05Z

3

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.

answered Nov 18, 2015 at 1:02

double_j

1,7161 gold badge19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Miguel Rentes · Accepted Answer · 2020-11-30 16:22:38Z

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].

Collectives™ on Stack Overflow

Using pymssql, how to call stored procedure with output

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related