Коварные переменные - scrappyCoco/SQL-Note GitHub Wiki

В большинстве современных языках программирования имеется понятие области видимости переменной. Придя с этой мыслью в мир SQL приходится столкнуться с проблемой. Здесь так не работает. В качестве примера прошу рассмотреть простой пример на C#:

using System;

namespace ConsoleApp1
{
	static class Program
	{
		static void Main()
		{
			int?[] sourceData = {
				123,
				null,
				456
			};

			for (int index = 0; index < sourceData.Length; index++)
			{
				int? myComputedValue = null;
				int? value = sourceData[index];
				if (value != null)
				{
					myComputedValue = value * 10;
				}
				
				Console.WriteLine(myComputedValue);
			}
		}
	}
}

Не сложно догадаться, как будет выглядеть результат:

1230

4560

А теперь тот же код, но на T-SQL:

DECLARE @SourceData TABLE (
  Id    INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
  Value INT
);

INSERT INTO @SourceData (Value)
VALUES
  (123),
  (NULL),
  (456);

DECLARE @id INT,
  @value INT;

DECLARE sourceCursor CURSOR FOR
  SELECT
    Id,
    Value
  FROM @SourceData;

OPEN sourceCursor;

FETCH NEXT FROM sourceCursor INTO @id, @value;

WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @myComputedValue INT;

  IF @value IS NOT NULL
    BEGIN
      SET @myComputedValue = @value * 10;
    END

  SELECT @myComputedValue;

  SET @value = NULL;
  FETCH NEXT FROM sourceCursor INTO @id, @value;
END
CLOSE sourceCursor;
DEALLOCATE sourceCursor;

Результат получился отличный от предыдущего примера. Ведь мы ожидали, что второе значение будет null:

1230 1230 4560

На самом деле, для получения нужного результата в SQL необходимо исправить

DECLARE @myComputedValue INT;

на

DECLARE @myComputedValue INT = NULL;

Дело в том, что переменные объявляются в начале выполнения пакета и переменные, объявленные внутри вложенных блоков будут так же доступны из других блоков. Для наглядности привожу модифицированный пример:

DECLARE @sourceData TABLE (
  Id    INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
  Value INT
);

INSERT INTO @sourceData (Value)
VALUES
  (123),
  (NULL),
  (456);

DECLARE @id INT,
  @value INT;

DECLARE sourceCursor CURSOR FOR
  SELECT
    Id,
    Value
  FROM @sourceData;

OPEN sourceCursor;

FETCH NEXT FROM sourceCursor INTO @id, @value;

WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @myComputedValue INT;

  IF @value IS NOT NULL
    BEGIN
      SET @myComputedValue = @value * 10;
    END

  SET @value = NULL;
  FETCH NEXT FROM sourceCursor INTO @id, @value;
END
CLOSE sourceCursor;
DEALLOCATE sourceCursor;

SELECT @myComputedValue;

В данном случае переменная @myComputedValue объявлена внутри блока, а доступна снаружи. Во избежание подобной путаницы, предлагаю объявлять переменные явно в начала скрипта.