CODEWITHSUNDEEP
pythonsql-serverexecpymssql
SomeGuyOnAComputer
SomeGuyOnAComputer

Reputation: 6208

Using pymssql, how to call stored procedure with output

  1. 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()

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

Upvotes: 2

Views: 8457

Answers (2)

Miguel Rentes
Miguel Rentes

Reputation: 1003

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

Upvotes: 1

double_j
double_j

Reputation: 1706

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.

Upvotes: 3

Related Questions