So I am currently trying to connect a stored procedure in sql server to a python program. I connect to the server fine and can grab tables but when I try to run my stored procedure I get a "This result does not return rows" error. Here is the code I am using to call the stored procedure:
import pyodbc
import sqlalchemy as sal
engine = sal.create_engine('mssql+pyodbc://{}:{}@{}/{}?driver={}'.format(username,password,server,database,driver_2))
previous_days_start_value = 1
previous_days_end_value = 10
sql_query = sal.text("EXEC clk.get_codes_pdf :previous_days_start, :previous_days_end")
pdf_df = pd.read_sql_query(sql_query, engine, params={"previous_days_start":previous_days_start_value, "previous_days_end":previous_days_end_value})
This is basically the stored procedure I am calling:
ALTER PROCEDURE [gen].[get_codes]
@previous_days_start bigint, @previous_days_end bigint
AS
BEGIN TRY
SET NOCOUNT ON;
select * from #Table;
END Try
And when I execute this inside of sql I do get a table. I can execute this like:
exec gen.get_codes 30,10
And the table prints out perfectly.
Any help would be very appreciated
I want to return the temp table from my stored procedure. I have tested the stored procedure using:
exec gen.get_codes 30,10
Where I do get a table in return. I also tried adding
SET NOCOUNT ON;
But I still could not get a table returned. It seems to make the connection but then no table is returned into my dataframe
Pandas read_sql_query is for reading an SQL result-set into a Pandas DataFrame.
The error that you're getting suggests that your stored-procedure isn't intended to return a result-set. Using the SqlAlchemy engine (the engine variable in your code sample) directly would be more suitable.
As per the comments, you should also check that stored-procedure that you're testing is the same one that the code is trying to run.