Stored Procedures?

[couzhei]

I'm a little confused using your lib for Microsoft SQL Server. Any idea how to invoke a storedProcedure as well as listing its result (the second part is more important), similar to mssql library?

[couzhei]

https://pymssql.readthedocs.io/en/stable/pymssql_examples.html#calling-stored-procedures

[phenobarbital]

Hi!, sure!, I write two examples how to use it:

import asyncio
from asyncdb.drivers.sqlserver import sqlserver

async def call_stored_procedure():
    # Create a connection
    params = {
        "server": "your_server",
        "user": "your_username",
        "password": "your_password",
        "database": "your_database",
        "port": 1433  # Default SQL Server port
    }
    
    # Initialize the MS SQL Server driver
    driver = sqlserver(params=params)
    
    try:
        # Connect to the database
        async with await driver.connection() as conn:
        
            # Call a stored procedure with parameters
            # Example: stored procedure that takes parameters @customer_id and @order_date
            proc_name = "sp_get_customer_orders"
            parameters = {
                "customer_id": 12345,
                "order_date": "2025-03-01"
            }
            # The procedure method handles the callproc and returns results
            result, error = await conn.procedure(proc_name, **parameters)
        
            if error:
                print(f"Error executing stored procedure: {error}")
                return
        
            # Process the results
            if result:
                print(f"Found {len(result)} orders:")
                for row in result:
                    print(f"Order ID: {row.get('order_id')}, Amount: {row.get('total_amount')}")
            
    except Exception as e:
        print(f"Unexpected error: {e}")

# Run the async function
if __name__ == "__main__":
    asyncio.run(call_stored_procedure())

I created another method called "exec" with pagination support but only can be used in a few procedures with pagination:

async def call_paginated_procedure():
    # Create a connection (same as above)
    params = {
        "server": "your_server",
        "user": "your_username",
        "password": "your_password",
        "database": "your_database"
    }
    
    driver = sqlserver(params=params)
    
    try:
        async with await driver.connection() as conn:
        
            # Call a paginated stored procedure
            # Where @page_size indicates how many records per page
            # and @page_num is the page index parameter
            proc_name = "sp_get_all_products"
            parameters = {
                "category_id": 5,
                "page_size": 100,
                "page_num": 1  # Starting page
            }
        
            # The exec method with pagination parameters
            # page: parameter name that contains the number of records per page
            # idx: parameter name for the page index
            result, error = await conn.exec(
                proc_name,
                paginated=True,
                page="page_size",  # Name of the parameter that defines records per page
                idx="page_num",    # Name of the parameter that defines the page index
                **parameters
            )
        
            if error:
                print(f"Error executing paginated procedure: {error}")
                return
        
            if result:
                print(f"Retrieved {len(result)} products in total")
                for row in result:
                    print(f"Product: {row.get('product_name')}, Price: {row.get('unit_price')}")
            else:
                print("No products found")
            
    except Exception as e:
        print(f"Unexpected error: {e}")
    finally:
        if driver._connected:
            await driver.close()

# Run the async function
if __name__ == "__main__":
    asyncio.run(call_paginated_procedure())

Let me know if this works for you.