SQL DECLARE not retaining scope when running multiple statement

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


4 comments

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

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

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

Please sign in to leave a comment.