title: Django execute stored procedure - different data types, multiple parameters and different columns from model
body: <p>Here is a list of what works and what doesn't, along with the errors. when trying to send in an decimal/money data type variable. I'm not sure if the issue is with different data types, or that more than 3 arguments are passed in. The first 2 parameters look like numbers, but they are sent in as strings.
  The database is SQL Server 2017</p>
  
  <p>I noticed the data types of the parameters are set based on the model - does this change anything?
  <strong>param1 - '10', param2 is '-100' and param3: Decimal('100')</strong></p>
  
  <pre><code># WORKS - there's a charfield/nvarchar datatype, one param only (pyodbc/mssql)
  # cursor.execute('EXEC [dbo].[sproc2] @charfield-param={}'.format(stringValue))
  
  # DOESNT WORK
  # cursor.execute('EXEC [dbo].[sproc1] @charfield-param1=%s @charfield-param2=%s @money-decimal-param=%d', (charfield-param1, charfield-param2, 100000))
  # %d format: a number is required, not str
  
  # cursor.execute('EXEC [dbo].[sproc1] @charfield-param1=%s @charfield-param2=%s @money-decimal-param=%s', (charfield-param1, charfield-param2, 100000))
  # ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@charfield-param2'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")
  
  # cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}'.format(charfield-param1, charfield-param2, 100000))
  # ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@charfield-param2'. (102) (SQLExecDirectW)")
  # ignored_wrapper_args  
  # (False,
  # {'connection': &lt;sql_server.pyodbc.base.DatabaseWrapper object at 0x04B21470&gt;,
  # 'cursor': &lt;django.db.backends.utils.CursorDebugWrapper object at 0x04CEDD10&gt;})
  # params    
  # None
  # self  
  # &lt;django.db.backends.utils.CursorDebugWrapper object at 0x04CEDD10&gt;
  # sql   
  # ('EXEC [dbo].[sproc1] @charfield-param1=10 @charfield-param2=-100 '
  # '@money-decimal-param=100000')
  
  # DOESNT WORK
  # cursor.execute('EXEC [dbo].[sproc1] @charfield-param1=%s @charfield-param2=%s @money-decimal-param=%s', (charfield-param1, charfield-param2, 100000))
  # # ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@charfield-param2'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")
  
  # cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}'.format(charfield-param1, charfield-param2, money-decimal-param3))
  # ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@charfield-param2'. (102) (SQLExecDirectW)")
  # params    
  # ()
  # self  
  # &lt;sql_server.pyodbc.base.CursorWrapper object at 0x03FD46B0&gt;
  # sql   
  # ('EXEC [dbo].[sproc1] @charfield-param1=10 @charfield-param2=-100 '
  # '@money-decimal-param=200000')
  
  # cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}'.format(charfield-param1, charfield-param2).format(money-decimal-param3))
  # ignored_wrapper_args  
  # (False,
  # {'connection': &lt;sql_server.pyodbc.base.DatabaseWrapper object at 0x04019470&gt;,
  # 'cursor': &lt;django.db.backends.utils.CursorDebugWrapper object at 0x04019790&gt;})
  # params    
  # None
  # self  
  # &lt;django.db.backends.utils.CursorDebugWrapper object at 0x04019790&gt;
  # sql   
  # ('EXEC [dbo].[sproc1] @charfield-param1=10 @charfield-param2=-100 '
  # '@money-decimal-param=200000')
  
  cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}', (str(charfield-param1), str(charfield-param2), int(money-decimal-param3)))
  # not all arguments converted during string formatting
  
  # cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @money-decimal-param={}', (str(charfield-param1), str(charfield-param2), int(100000)))
  # Error: '&lt;' not supported between instances of 'NoneType' and 'int'
  </code></pre>
  
  <p>Changing the stored proc signature to expect 3 charfields, instead of 2 and a money field, doesn't fix my problem. So, looks like a formatting issue? I'm very new to Python and django, so this might be a really simple one to fix!</p>
  
  <pre><code>cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @charfield-param3={}'.format(str(charfield-param1), str(charfield-param2), charfield-param3))
  
  cursor.execute('EXEC [dbo].[sproc1] @charfield-param1={} @charfield-param2={} @charfield-param2={}'.format(charfield1, charfield2).format(charfield3))
  IndexError: tuple index out of range
  </code></pre>
  
  <p>Edit: <strong>An integer is returned, along with differently named columns from different tables (not necessarily mapped to the mapped table/model).</strong> </p>
  
  <p>Error details:</p>
  
  <pre><code> cursor.execute('EXEC [dbo].[stored_proc] @Param1={} @Param2={} @NewParam3={}'.format(strparam1, strparam2,strparam3))        
  </code></pre>
  
  <p>Environment:
  Local vars:</p>
  
  <pre><code>ignored_wrapper_args    
  (False,
   {'connection': &lt;sql_server.pyodbc.base.DatabaseWrapper object at 0x05E96490&gt;,
    'cursor': &lt;django.db.backends.utils.CursorDebugWrapper object at 0x060D51B0&gt;})
  params: None
  self    : &lt;django.db.backends.utils.CursorDebugWrapper object at 0x060D51B0&gt;
  sql: ('EXEC [dbo].[stored_proc] @Param1=10 '
   '@Param2=-100 @NewParam3=100000')
  
  Request Method: POST
  Request URL: http://localhost:8000/app-url/
  
  Django Version: 2.1.1
  Python Version: 3.7.0
  Installed Applications:
  ['django.contrib.admin',
   'django.contrib.auth',
   'django.contrib.contenttypes',
   'django.contrib.sessions',
   'django.contrib.messages',
   'django.contrib.staticfiles',
   'app_name']
  Installed Middleware:
  ['django.middleware.security.SecurityMiddleware',
   'django.contrib.sessions.middleware.SessionMiddleware',
   'django.middleware.common.CommonMiddleware',
   'django.middleware.csrf.CsrfViewMiddleware',
   'django.contrib.auth.middleware.AuthenticationMiddleware',
   'django.contrib.messages.middleware.MessageMiddleware',
   'django.middleware.clickjacking.XFrameOptionsMiddleware']
  </code></pre>
  
  <p>Traceback:</p>
  
  <pre><code>File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in _execute
    83.                 return self.cursor.execute(sql)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\sql_server\pyodbc\base.py" in execute
    546.             return self.cursor.execute(sql, params)
  
  The above exception (('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@Param2'. (102) (SQLExecDirectW)")) was the direct cause of the following exception:
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\core\handlers\exception.py" in inner
    34.             response = get_response(request)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\core\handlers\base.py" in _get_response
    126.                 response = self.process_exception_by_middleware(e, request)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\core\handlers\base.py" in _get_response
    124.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\views\generic\base.py" in view
    68.             return self.dispatch(request, *args, **kwargs)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\views\generic\base.py" in dispatch
    88.         return handler(request, *args, **kwargs)
  
  File "C:\Github\project-name\app_name\views.py" in post
    90.             results = ModelName.get_stored_proc_data(str(param1), str(param2), decimalParam3)
  
  File "C:\Github\project-name\app_name\models.py" in get_stored_proc_data
    89.             cursor.execute('EXEC [dbo].[stored_proc] @Param1={} @Param2={} @NewParam3={}'.format(param1, param2,decimalParam3))        
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in execute
    100.             return super().execute(sql, params)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in execute
    68.         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in _execute_with_wrappers
    77.         return executor(sql, params, many, context)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in _execute
    85.                 return self.cursor.execute(sql, params)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\utils.py" in __exit__
    89.                 raise dj_exc_value.with_traceback(traceback) from exc_value
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\django\db\backends\utils.py" in _execute
    83.                 return self.cursor.execute(sql)
  
  File "C:\Users\user_name\.virtualenvs\prioject_name\lib\site-packages\sql_server\pyodbc\base.py" in execute
    546.             return self.cursor.execute(sql, params)
  
  Exception Type: ProgrammingError at /app-url/class/
  Exception Value: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@Param2'. (102) (SQLExecDirectW)")
  </code></pre>