I am using python script and pyodbc library to connect to a MS Sql. ODBC Driver 17 for SQL Server.Then I use DECLARE to create a table variable:
SQL_QUERY = """
DECLARE @MyTable TABLE (ID INT, Name NVARCHAR(255));
INSERT INTO @MyTable (ID, Name) VALUES (1, 'John'), (2, 'Jane');
SELECT * FROM @MyTable;
"""
cursor.execute(SQL_QUERY)
records = cursor.fetchall() -----error
print(records)
However it gives me error:
records = cursor.fetchall()
^^^^^^^^^^^^^^^^^
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
I also try to use DECLARE to create a scalar variable:
SQL_QUERY = """
DECLARE @MyVariable INT;
SET @MyVariable = 42;
SELECT @MyVariable AS MyValue;
"""
cursor.execute(SQL_QUERY)
result = cursor.fetchone()
my_value = result.MyValue
print(f"The value of the declared variable is: {my_value}")
No error and the result is correct.
Why the first table variable return that weird error?? Can anyone give suggestions? Thanks!