Calling Stored Procedures

Stored Procedure Execution

The mssql-python module does not currently implement the callproc() method contrary to DBAPI specifications. Please see Known Issues and Unsupported Features. However, it supports calling stored procedures using {CALL ...} escape sequence within execute() function. This method allows you to execute stored procedures with or without parameters.

Calling Stored Procedures

No Parameters

To call a stored procedure named usp_NoParameters that does not require any parameters, you can use the following code:

from mssql_python import connect

# Establish a connection
conn = connect("Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;")

# Create a cursor object
cursor = conn.cursor()

# Call the stored procedure
cursor.execute("{CALL usp_NoParameters}")

# Close the connection
conn.close()

Input Parameters

To call a stored procedure that takes input parameters, you can pass the parameters as a tuple:

params = (42, "Arthur")
cursor.execute("{CALL usp_UpdateName (?,?)}", params)

Output Parameters and Return Values

The mssql-python module supports retrieving output parameters and return values from stored procedures. You can use an "anonymous code block" to execute the stored procedure and then select the output parameters and/or return values.

CREATE PROCEDURE [dbo].[example_procedure] 
    @input_param nvarchar(max) = N'', 
    @output_param nvarchar(max) OUTPUT
AS
BEGIN
    -- Emit a rowcount as the first "result"
    SELECT 1 AS dummy INTO #temp;

    -- Set output parameter
    SELECT @output_param = N'Output: You provided "' + @input_param + N'".';
    
    -- Return a couple of result sets
    SELECT N'Result set 1, row 1' AS col1
    UNION ALL
    SELECT N'Result set 1, row 2' AS col1;
    
    SELECT N'Result set 2, row 1' AS col2
    UNION ALL
    SELECT N'Result set 2, row 2' AS col2;
END

You can call this stored procedure and retrieve the output parameter in Python as follows:

sql = """\
SET NOCOUNT ON;
DECLARE @out nvarchar(max);
EXEC [dbo].[example_procedure] @input_param = ?, @output_param = @out OUTPUT;
SELECT @out AS output_value;
"""
params = ("Hello, World!", )
cursor.execute(sql, params)
rows = cursor.fetchall()
while True:
    print(rows)
    if cursor.nextset():
        rows = cursor.fetchall()
    else:
        break

This will produce the following output:

[('Result set 1, row 1', ), ('Result set 1, row 2', )]
[('Result set 2, row 1', ), ('Result set 2, row 2', )]
[('Output: You provided "Hello, World!".', )]

Notice that the result sets created by the stored procedure are returned first, followed by the result set with the output parameter as returned by the SELECT statement in the anonymous code block.

Example with Return Values

For a SQL Server stored procedure with a return value, you can use a similar approach:

sql = """\
SET NOCOUNT ON;
DECLARE @return_value int;
EXEC @return_value = [dbo].[another_example_procedure];
SELECT @return_value AS return_value;
"""
cursor.execute(sql)
return_value = cursor.fetchval()
print(return_value)

This will retrieve the return value from the stored procedure and print it.