Python - pyodbc call stored procedure with parameter name

Question

I need to call a SqlServer stored procedure from python2.7 via pyodbc module with input parameter name.

I tried based on documentation by input parameter order:

cursor.execute('{CALL [SP_NAME](?,?)}',
              ('value', 'value'))

It works, but I need to pass parameter name of stored procedure because order of stored procedure input parameter always changes. So I need to pass them by name.

cursor.execute('{CALL [SP_NAME](@param1name,@param2name)}',
              ('value', 'value'))

However this doesn't work. What's the correct syntax?

What driver are you using, and are you on Linux or Windows? Sometimes, the TDS and driver version can affect stored procedures. — FlipperPA
– FlipperPA, Commented Jun 21, 2015 at 15:44
@FlipperPA Thanks for your attention. I'm using Windows 8. And about driver i didn't get it. what do you mean? — Aida.Mirabadi
– Aida.Mirabadi, Commented Jun 22, 2015 at 5:05

Gord Thompson · Accepted Answer · 2015-12-09 12:55:17Z

I tested this using the following stored procedure in SQL Server 2008 R2:

CREATE PROCEDURE [dbo].[breakfast] 
    @person varchar(50) = 'nobody', 
    @food varchar(50) = 'tofu'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @person + ' likes to eat ' + @food
END

The Bad News ("CALL")

I found that

sql = """\
{ CALL breakfast (@food=?, @person=?) }
"""
params = ('bacon','Gord')
crsr.execute(sql, params)

gave inconsistent results.

With the {SQL Server Native Client 10.0} ODBC driver it ignored the parameter names and treated the parameters as positional, yielding ...

bacon likes to eat Gord

... and with the older {SQL Server} ODBC driver I just got the error

DataError: ('22018', '[22018] [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (0) (SQLExecDirectW)')

The Good News ("EXEC")

I found that

sql = """\
EXEC breakfast @food=?, @person=?
"""
params = ('bacon','Gord')
crsr.execute(sql, params)

gave me the following (correct) result using both ODBC drivers

Gord likes to eat bacon

Mi-Creativity · Accepted Answer · 2015-12-08 17:45:10Z

-3

This syntax appears to work for me:

cursor.execute('{CALL [SP_NAME](@param1name=?, @param2name=?)}',
('value', 'value'))

edited Dec 8, 2015 at 17:45

Mi-Creativity

9,66410 gold badges40 silver badges49 bronze badges

answered Dec 8, 2015 at 17:25

JesseC

4924 silver badges5 bronze badges

1 Comment

Aida.Mirabadi Over a year ago

Thanks for your attention. see @GordThompson's answer :)

Collectives™ on Stack Overflow

Python - pyodbc call stored procedure with parameter name

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related