Execute SQL Server stored procedure from python on database directly

Question

I want to run an existing SQL Server stored procedure on a database using Python. To clarify, I do not want to get the output in Python. I want the output to be saved directly in the database itself.

I connected to the database using pymssql using the following code:

storedProc = "Exec TEST;"
cursor.execute( storedProc )

This is the code of my procedure:

CREATE PROCEDURE dbo.TEST
as
set nocount on

IF OBJECT_ID('tempdb..#A', 'U')IS not NULL DROP TABLE #A
CREATE TABLE #A
([id]           [INT] identity(1,1), 
 [CustomerName] [VARCHAR](30) NULL, 
 [OrderCount]   [INT] NULL
)
ON [PRIMARY]

Insert into #A ([CustomerName],[OrderCount]) values('Rajjjj',22),('Kusum',3),('Akshita',4),('John',5),('Dan',6)
IF OBJECT_ID('dbo.TEST_A', 'U')IS NULL
CREATE TABLE dbo.TEST_A
(
 [CustomerName] [VARCHAR](30) NULL, 
 [OrderCount]   [INT] NULL
);
truncate table  dbo.TEST_A
insert into dbo.TEST_A

This code runs the procedure, but the output is returned to Python, which is not what I want.

You need to write a stored procedure that saves the results rather than returning them then... — Dale K, Oct 25 '21 at 20:02
The procedure doesn't return the results. It is only updating an existing table. But if you execute this procedure in python, it will return the table instead of updating it on the database, unless you have conn.commit() line (which I lacked) — Levani Zandarashvili, Oct 25 '21 at 21:23
Please don't add your answer to the question, you've already, correctly, added a self answer. — Dale K, Oct 25 '21 at 21:39
I still find that rather odd, since nothing in your SP returns data, I don't see how you are getting any data with or without the commit. But your problem is solved which I guess is the main thing. — Dale K, Oct 25 '21 at 21:40
@DaleK again, thanks for the tip. I thought adding answer there would be helpful. — Levani Zandarashvili, Oct 25 '21 at 21:43

score 1 · Answer 1 · answered Oct 25 '21 at 20:16

The query sent to the database must do the insert, then. Considering a table named [proc_output] with the same structure as the return of the procedure, the code would be:

storedProc = """
insert into proc_output
    Exec Procedure_Name;
"""
cursor.execute( storedProc )

Execute SQL Server stored procedure from python on database directly

1 Answers1