How to Call Stored Procedures with OUTPUT Parameters in Python

Summary: in this tutorial, you will learn to call a stored procedure with an OUTPUT parameter in Python.

This tutorial begins where the Calling a stored procedure from a Python program tutorial left off.

Creating a stored procedure with an output parameter in SQL Server

Step 1. Launch Microsoft SQL Server Management Studio (SSMS) and connect to the SQL Server.

Step 2. Create a new stored procedure by executing the following statement:

CREATE PROCEDURE CountCustomerByEmailDomain(
    @domain VARCHAR(255)
) AS
BEGIN
	DECLARE @CustomerCount INT;

    SELECT @CustomerCount = COUNT(*)
	FROM Customers
    WHERE Email LIKE '%' + '@' + @domain;

    SELECT @CustomerCount AS CustomerCount;
END;Code language: SQL (Structured Query Language) (sql)

Step 3. Call the stored procedure CountCustomerByEmailDomain:

DECLARE @CustomerCount INT;

EXEC CountCustomerByEmailDomain @Domain = 'gmail.com', 
	 @CustomerCount = @CustomerCount OUTPUT;Code language: SQL (Structured Query Language) (sql)

Output:

CustomerCount
-------------
72Code language: plaintext (plaintext)

Calling the stored procedure

Step 1. Modify the procedure.py module and add the following function:

def count_customer_by_email_domain(domain: str) -> int:
    # Connect to the SQL Server
    conn = create_connection()
    if conn is None:
        return 0

    # Call the stored procedure
    with (conn, conn.cursor() as cursor):
        cursor.callproc('CountCustomerByEmailDomain', (domain, 0))
        row = cursor.fetchone()
        return row[0] if row else 0Code language: Python (python)

How it works.

First, define a function count_customer_by_email_domain that accepts a domain and returns the number of customers whose email’s domain matches with the domain:

def count_customer_by_email_domain(domain: str) -> int:Code language: Python (python)

Second, connect to SQL Server by calling the create_connection() function and return 0 if the connection fails:

conn = create_connection()
if conn is None:
    return 0Code language: Python (python)

Third, manage the Connection and Cursor objects using the with statement:

with (conn, conn.cursor() as cursor):Code language: Python (python)

Fourth, call the stored procedure CountCustomerByEmailDomain:

cursor.callproc('CountCustomerByEmailDomain', (domain, 0))Code language: Python (python)

When calling the callproc method, we pass the second argument as a tuple (domain, 0) that includes two elements domain and 0.

The second element of the tuple (0) represents the placeholder for the output parameter of the stored procedure CountCustomerByEmailDomain.

Step 2. Modify the main.py module to use the count_customer_by_email_domain() function

import logging, sys
from procedure import count_customer_by_email_domain


# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)


customer_count = count_customer_by_email_domain('gmail.com')
logging.info(customer_count)Code language: Python (python)

How it works.

First, import the count_customer_by_email_domain function from the procedure module:

from procedure import count_customer_by_email_domainCode language: Python (python)

Second, call the function to count customers whose email domains are gmail.com:

customer_count = count_customer_by_email_domain('gmail.com')Code language: Python (python)

Third, log the customer count:

logging.info(customer_count)Code language: Python (python)

Step 3. Open your terminal and run the main.py file:

python main.pyCode language: Python (python)

Output:

INFO:72Code language: Python (python)

The output indicates that there are 72 customers whose email domains are gmail.com.

Download the project source code

Summary

Use callproc() method of the Cursor object to call a stored procedure.
Add placeholders to the tuple passed to the callproc() method for the OUTPUT parameters.

Was this tutorial helpful?