How I use a local variable in a SQL fiddle query

Question

I was trying to create a sqlfiddle

DECLARE @binsize INT = 5;
select 5;
select @binsize; <-- this fail.
select 6;

And got this error.

Must declare the scalar variable "@binsize".

I have found sample like this and works ok in sql server.

USE AdventureWorks2012;
GO
DECLARE @find varchar(30); 
/* Also allowed: 
DECLARE @find varchar(30) = 'Man%'; 
*/
SET @find = 'Man%'; 
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p 
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;

SOLVE

Going with the GO change solve the issue. but still open other issues.

DECLARE @binsize INT = 5;
select @binsize + 2 ;
select @binsize + 5 ;
GO

Only return 7

Note that semicolons should be used wherever possible in supportive dialects, but yes, that is the (questionable) solution in this case. I wouldn't recommend skimping on them in T-SQL, though, as (A) that's just good ANSI, and (B) MS are on record as stating that they might become mandatory in a future release. Unlikely, but worth starting early... — underscore_d, Jul 17 '15 at 19:48
or select 'go' as query terminator in the 4th combo box below the query — James Z, Jul 17 '15 at 20:01
Using `GO` as the query terminator is a much better solution than dropping semi-colons. — Aaron Bertrand, Jul 17 '15 at 20:06
@AaronBertrand, You are right is a duplicated, but the solution there doesn't work. You can check the [fiddle](http://sqlfiddle.com/#!3/d41d8/43750) on the selected solution doesn't even compile. — Juan Carlos Oropeza, Jul 17 '15 at 20:44
@Juan The OP there chose the wrong answer, nothing the site can do about that; also, the fact that their sample query doesn't compile doesn't make the solution wrong (it's just not the optimal answer). In either case, the fact that the OP chose the wrong solution doesn't make the questions different (and that can be further equalized through voting, hint, hint). Your solution should be the other answer on that question, which is to change the query terminator from `;` to `GO`. — Aaron Bertrand, Jul 17 '15 at 20:51
@AaronBertrand I just downvote the question and I lost 1 reputation too. I dont understand how this work :(. Anyway I understand the question is duplicated. But there one answer is wrong, and the other is incomplete. The answer here was more complete — Juan Carlos Oropeza, Jul 17 '15 at 21:04

score 2 · Accepted Answer · edited Jul 29 '20 at 18:23

2

As @AaronBertrand suggested, using GO is a better practice like below:

DECLARE @binsize INT = 5;
select @binsize;
GO

select 5;

Note: In the right hand corner on SQLFiddle site, you will need to switch the query terminator to use GO keyword.

SQL Fiddle Demo

edited Jul 29 '20 at 18:23

Community

1
1

answered Jul 17 '15 at 19:59

FutbolFan

13,235
3
23
35

While this works in this simple case, it is [not exactly a best practice](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/09/03/ladies-and-gentlemen-start-your-semi-colons.aspx), and will break when the OP uses a query construct that actually *requires* semi-colons. – Aaron Bertrand Jul 17 '15 at 20:07
@AaronBertrand Good point! I actually like your idea of using GO. I didn't think of that. Thanks! – FutbolFan Jul 17 '15 at 20:09
Sorry but that doesnt work for me. Im only getting one 5 instead of two. If you change `select 7` you may see the difference [FIDDLE](http://sqlfiddle.com/#!6/9eecb7/1195) – Juan Carlos Oropeza Jul 17 '15 at 20:40
@JuanCarlosOropeza My apologies if I misunderstood your question. But, why are you expecting a `2` when you are clearly setting your variable to `5`? – FutbolFan Jul 17 '15 at 20:44
sorry I mean a double 5. not a two. In the result only show a single 5 – Juan Carlos Oropeza Jul 17 '15 at 20:46
Oh okay. If you are expecting a 5 twice, you can just `select 5` outside your `GO` statement something like above. – FutbolFan Jul 17 '15 at 20:50
2

@Juan SQLfiddle has a couple of limitations that I've seen, one is that it can only output one resultset per batch. Here is a slightly modified query that works fine: http://sqlfiddle.com/#!6/9eecb7/1217 – Aaron Bertrand Jul 17 '15 at 20:55
1

@AaronBertrand I just realize I cant do two `SELECT` using the @variable. The second `SELECT` was just for debug, the idea was use the @variable twice. But as you mention cant be done. – Juan Carlos Oropeza Jul 17 '15 at 21:02
@JuanCarlosOropeza I believe credit solely goes to Aaron for pointing you out in the right direction. – FutbolFan Jul 17 '15 at 21:13
1

You also have the credit. I more visual type person. I didnt understand the problem until saw the fiddle running. I wasnt uderstanding what `USING GO` mean. – Juan Carlos Oropeza Jul 17 '15 at 21:17
I am glad that you found your answer! I learn new thing everyday here at SO. – FutbolFan Jul 17 '15 at 21:20

How I use a local variable in a SQL fiddle query

1 Answers1