The Wayback Machine - https://web.archive.org/web/20200611021817/https://github.com/JSQLParser/JSqlParser/issues/913
Skip to content

/ JSqlParser

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQL Server variable assignment in a select does not parse | SELECT @var = 1 #913

Open
cdandoy opened this issue Dec 21, 2019 · 3 comments · May be fixed by #914
Open

SQL Server variable assignment in a select does not parse | SELECT @var = 1 #913

cdandoy opened this issue Dec 21, 2019 · 3 comments · May be fixed by #914

Comments

@cdandoy
Copy link

@cdandoy cdandoy commented Dec 21, 2019

Describe the bug
SQL Server supports assigning a value to a variable from a selected expression.
For example:

DECLARE @var INT;
CREATE TABLE tableName (id INT);

SELECT @var = 1;
SELECT @var = id FROM tableName;

To Reproduce
assertSqlCanBeParsedAndDeparsed("SELECT @var = 1");

System

  • SQL Server 2017
  • JSqlParser 3.1
cdandoy pushed a commit to cdandoy/JSqlParser that referenced this issue Dec 21, 2019
cdandoy
JSQLParser#913 SQL Server variable assignment in a select does not pa…
14ce1be 
…rse | SELECT @var = 1

This fixes JSQLParser#913 but breaks "SELECT @@sessions.tx_read_only".
Unfortunately, I don't know JavaCC enough to fix both.
@cdandoy cdandoy linked a pull request that will close this issue Dec 21, 2019
Open
cdandoy pushed a commit to cdandoy/JSqlParser that referenced this issue Dec 21, 2019
cdandoy
JSQLParser#913 SQL Server variable assignment in a select does not pa…
0059489 
…rse | SELECT @var = 1

Added asserts
@wumpz
Copy link
Member

@wumpz wumpz commented Dec 21, 2019

Do I see it right, that every expression is assignable in this form?
@cdandoy
Copy link
Author

@cdandoy cdandoy commented Dec 21, 2019

Yes it is.
Here is another example (tested on SQL Server 2017) that calculates 5!

DROP TABLE tableName;
CREATE TABLE tableName (id INT);
INSERT INTO tableName VALUES (1),(2),(3),(4),(5)

DECLARE @var INT = 1;
SELECT @var = @var * id FROM tableName;
SELECT @var -- 120
@cdandoy
Copy link
Author

@cdandoy cdandoy commented Dec 21, 2019

See https://docs.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-2017#examples
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
2 participants
@wumpz @cdandoy
You can’t perform that action at this time.