[Solved]-Python – pyodbc call stored procedure with parameter name

25πŸ‘

βœ…

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
πŸ‘€Gord Thompson

-3πŸ‘

This syntax appears to work for me:

cursor.execute('{CALL [SP_NAME](@param1name=?, @param2name=?)}',
('value', 'value'))
πŸ‘€JesseC

Leave a comment