Python – pyodbc call stored procedure with parameter name

by

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

More Related Contents:

  1. T-SQL stored procedure that accepts multiple Id values
  2. Select columns from result set of stored procedure
  3. SQL Server: Query fast, but slow from procedure
  4. Retrieve column definition for stored procedure result set
  5. Dapper.NET and stored proc with multiple result sets
  6. How to list all dates between two dates [duplicate]
  7. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  8. EXEC sp_executesql with multiple parameters
  9. Connecting to MS SQL Server with Windows Authentication using Python?
  10. How do I find a stored procedure containing ?
  11. How do I search an SQL Server database for a string?
  12. Calling an API from SQL Server stored procedure
  13. Handling date in SQL Server
  14. SQL Server history table – populate through SP or Trigger?
  15. Retrieve data from stored procedure which has multiple result sets
  16. Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
  17. Calling stored procedure using VBA
  18. Stored Procedure and Permissions – Is EXECUTE enough?
  19. Avoid Naming User Stored Procedures SP% or SP_%
  20. Execute stored procedure with an Output parameter?
  21. Getting a warning when using a pyodbc Connection object with pandas
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. Stored procedure – return identity as output parameter or scalar
  24. How to find a text inside SQL Server procedures / triggers?
  25. SQL Server stored procedure return code oddity
  26. Populate Dataset With Table Names From Stored Procedure
  27. How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?
  28. ODBC Call Failed with stored procedure – Pass through query
  29. Why can’t Entity Framework see my Stored Procedure’s column information?
  30. Pyodbc: Login Timeout Error

Leave a Comment