- Notifications
You must be signed in to change notification settings - Fork11
Calling Stored Procedures
The mssql-python module does not currently implement thecallproc() method contrary to DBAPI specifications. Please seeKnown Issues and Unsupported Features. However, it supports calling stored procedures using{CALL ...} escape sequence withinexecute() function. This method allows you to execute stored procedures with or without parameters.
To call a stored procedure named usp_NoParameters that does not require any parameters, you can use the following code:
frommssql_pythonimportconnect# Establish a connectionconn=connect("Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;")# Create a cursor objectcursor=conn.cursor()# Call the stored procedurecursor.execute("{CALL usp_NoParameters}")# Close the connectionconn.close()
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)
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) OUTPUTASBEGIN-- Emit a rowcount as the first "result"SELECT1AS dummy INTO#temp;-- Set output parameterSELECT @output_param= N'Output: You provided "'+ @input_param+ N'".';-- Return a couple of result setsSELECT N'Result set 1, row 1'AS col1UNION ALLSELECT N'Result set 1, row 2'AS col1;SELECT N'Result set 2, row 1'AS col2UNION ALLSELECT 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()whileTrue:print(rows)ifcursor.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.
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.