SQL DECLARE not retaining scope when running multiple statement

Avatar
Gilbert Grant
创建于

Hi, I have an issue when running multiple SQL statements through IDEA:

DECLARE @x int
SELECT * FROM MyTable WHERE id = @x
go


DECLARE @y varchar(max)
SELECT * FROM MyTable WHERE name = @y
go


...in this situation, the console spits out an error - @x is not declared. But if i run the scripts (between the go's) one by one it works fine.
How can I get this to work for the whole script?
~gilbert


Votes

0

分享

4 条评论
Avatar
Gregory Shrago
创建于

I currently do not know the other way but runing both statements together using JDBC.
Declared variables are accessible within the same batch block.

See my test output:

sql> DECLARE @y VARCHAR(MAX)
[2013-05-24 17:51:51] completed in 5 ms
sql> SELECT
  *
FROM sys.all_columns
WHERE collation_name like @y
[2013-05-24 17:51:53] [S0002][137] Must declare the scalar variable "@y".
sql> DECLARE @y VARCHAR(MAX)
SELECT
  *
FROM sys.all_columns
WHERE collation_name like @y
[2013-05-24 17:51:57] 0 rows retrieved in 19/66 ms

0
Avatar
Gilbert Grant
创建于

My question is more IntelliJ (and maybe JDBC) than SQL - I want to run the entire script (in my example) in one batch (ctrl-shift-f10). Problem is, when i have multiple statemt's with "go" the variable decalrations seem to get separated from the usages (and I do ensure the declarations are in the same block as their usages) - this happens only in IntelliJ, and works fine in SSMS.
~gilbert

0
Avatar
Gregory Shrago
创建于

Now I see. This is related to "Run Script" functionality that doesn't handle T-SQL correctly. Please file a ticket at http://youtrack.jetbrains.com .

0
Avatar
Gilbert Grant
创建于

Logged - thanks: http://youtrack.jetbrains.com/issue/IDEA-107916

0

请先登录再写评论。