Locally enclose a code block in SQL

Question

In Java you can do the following to locally scope variables within a method:

public void blah() {
    {
     int a = 1;
     int b = 2;
    }
    {
     int a = 3;
     int b = 4;
    }
}

I don't even know what the name for this technique is, I've only needed to use it once or twice. In my current situation, this may really come in handy with this SQL project.

Is this even possible in SQL?

---

I'm using MS SQL Server... To give more context to the situation:

We have several sql scripts stored as files which help perform various operations on a database. These scripts are fairly large and were designed to be run independently. Sometimes, we need to run several of these scripts together and deliver in a single file.

Since most of these script have common variables we run into a conflict when joining these scripts together. Of course, it is easy to move all variable declaration to the beginning of the file, but the goal is to have this process automated.

Answer 1

In MySQL variable declarations must come at the beginning of the subroutine/stored procedure, like a C function's variables must be.

Answer 2

Trying to compile the Java code example you give (wrapped in a class helo) ## Heading ##produces:

$ javac helo.java 
helo.java:7: a is already defined in blah()
        int a = 3;
            ^
helo.java:8: b is already defined in blah()
        int b = 4;
            ^
2 errors

So, no, you cannot do that in Java (C, yes; C++, yes; Java, no).

SQL per se doesn't have lexical scoping, although some stored-procedure dialects embedded in relational DBs surely may (but it's hard to help w/o knowing the exact dialect). In SQL proper, you qualify otherwise-homonymous fields with tablename. prefixes.

Answer 3

In SQL Server, you can't scope in blocks. As long as DECLARE is before SET you're OK. The DECLAREs don't have to be at the start of the batch or code either

BEGIN
    DECLARE @a int
END
BEGIN
    SET @a = 1
END

IIRC, the same applies to Sybase too.

Answer 4

SQL is a declarative language (i.e. focuses on the 'whats' and not the 'hows'). Hence its definition (ansi) does not contain procedurals and blocks.

Most vendor implementations fill this void by embedding their own procedural language - TSQL in case of Microsoft, PL/SQL in case of Oracle etc. (Not sure of Mysql and SQLlite etc) - and even they dont do an exact feature match with Java (No anonymous codeblocks).

Each SQL operation works on a set, and returns a set.

Thus "mytable" is a set, "select from mytable" is a set , and "select from (select from mytable) " is a set.

So its kind of not possible to drill down into a lower level of granularity, perhaps "columns" or pseudo columns are the 'variables'

Oracle defines a "select from dual" operation to guarantee a 'loop' of 1 (i.e. a select statement that returns exactly 1 row)