Can't use variables (or transactions) in MS SQL Server

[outcomes-dale-sedivec]

What happens?

Connect to a Microsoft SQL Server using Jupyter Notebooks and JupySQL, then run a cell such as:

%%sql

declare @x int  = 42;

select @x;

Expected result: A 1x1 result set containing 42

Actual result: SQL Server error that @x is not defined (sorry person with GitHub handle "x")

To Reproduce

See above. I'm reproducing this using PyODBC and MS's ODBC driver talking to SQL Server 2019, but I suspect you'll be able to reproduce this against any combination of drivers and SQL Server versions from the past decade or more.

My suspicion for why this happens is Jupysql splitting the cell into separate statements and executing each statement separately. AFAIK, with SQL Server and PyODBC, each call to the connection's execute method is a separate "batch" in SQL Server parlance. The scope of any variable is apparently limited to a single batch.

Is there any possibility for some version of the %%sql magic that doesn't attempt to split statements? That would

1. Allow me to use variables
2. Perhaps ease the way to letting me control a transaction within a cell?

To this last point, I currently have to stop using Jupysql the second I want to do some DML within a transaction, due to the nearby code that sniffs out BEGIN refusing to let me start a transaction. I tried to find out why this is here, but IIRC this code was born first in catherinedevlin/ipython-sql, and I don't think I could understand why it was ever necessary there, except maybe as a very brute-force tool to solve some other problem at the time. Not being able to use transactions in my notebooks has been an occasional source of pain. :)

Thank you very much for maintaining this excellent software!

OS:

Linux

JupySQL Version:

0.10.14

Full Name:

Dale Sedivec

Affiliation:

Outcomes