Usage

Django-mssql is a Django database backend and supports the interface for the paired Django version. It should behave the same as the core backends.

Executing Custom SQL

Please refer to the Django documentation for Executing custom SQL directly.

Stored Procedures

Django-mssql provides support for executing stored procedures, with and without parameters. The main function that should be used to execute a stored procedure is callproc. callproc will allow executing stored procedures with both input and output parameters, integer return values, and result sets.

def callproc(self, procname, parameters=None):
    """Call a stored database procedure with the given name.

    The sequence of parameters must contain one entry for each
    argument that the sproc expects. The result of the
    call is returned as modified copy of the input
    sequence. Input parameters are left untouched, output and
    input/output parameters replaced with possibly new values.

    The sproc may also provide a result set as output,
    which is available through the standard .fetch*() methods.

    Extension: A "return_value" property may be set on the
    cursor if the sproc defines an integer return value.
    """

Example:

This example assumes that there exists a stored procedure named uspDoesSomething that expects two parameters (int and varchar), and returns 1 when there is a result set.

from django.db import connection

cursor = connection.cursor()
try:
    cursor.callproc('[dbo].[uspDoesSomething]', [5, 'blah'])

    if cursor.return_value == 1:
        result_set = cursor.fetchall()
finally:
    cursor.close()

It is also possible to use the cursor’s execute method to call a stored procedure, but return_value will not be set on the cursor and output parameters are not supported. This usage is intended for calling a stored procedure that returns a result set or nothing at all.

Example:

from django.db import connection

cursor = connection.cursor()
try:
    cursor.execute('EXEC [dbo].[uspFetchSomeData]')
    result_set = cursor.fetchall()
finally:
    cursor.close()

RawStoredProcedureManager

The RawStoredProcedureManager provides the raw_callproc method that will take the name of a stored procedure and use the result set that it returns to create instances of the model.

Example:

from sqlserver_ado.models import RawStoredProcedureManager

class MyModel(models.Model):
    ...

    objects = RawStoredProcedureManager()

sproc_params = [1, 2, 3]
MyModel.objects.raw_callproc('uspGetMyModels', sproc_params)

Note

The db_column name for the field must match the case of the database field as returned by the stored procedure, or the value will not be populated and will get fetched by the ORM when the field is later accessed.

New in version 1.2.