本記事の目的

本来であればSQLserverの原理原則を理解してから記事を書くべきですが、
~~公式ドキュメントを読んでもよくわからないため、~~ 今回は「このクエリが動いた」「このクエリが動かなかった」という記録にとどめています。

至らない点があればアドバイスもらえると助かります。

簡単な結論

WITH句を使用する場合は、SELECTにて変数を代入できる。SETでは代入できない。

様々なクエリのテスト

テスト1 setで実数を代入する(動く)

DECLAREで変数を宣言し、SETで変数を指定します。クエリは動きます。

クエリの例

DECLARE @hoge int;
SET @hoge=5

SELECT @hoge as test_val

###テスト2 setでクエリから出てきた値を代入する(動く)
DECLAREで変数を宣言し、SETでクエリから抽出された値を代入します。クエリは動きます。

クエリの例

DECLARE @hoge int
SET @hoge = (  SELECT count(hoge_id)
                FROM hoge_table
                )

SELECT @hoge as test_val

###テスト3 setでクエリから出てきた値を複数代入する(動く)
DECLAREで変数を宣言し、SETでクエリから抽出された複数の値を各々の変数に代入します。クエリは動きます。

クエリの例

DECLARE @hoge1 int
DECLARE @hoge2 int

SET @hoge1 = (  SELECT count(hoge_id)
                FROM hoge_table
                )
SET @hoge2 = (  SELECT sum(hoge_id)
                FROM hoge_table
                )

SELECT @hoge1 as test_val_count,
       @hoge2 as test_val_sum

###テスト4 setとwith句を用い、クエリから出てきた値を代入する(動かない)
DRCLAREで変数を宣言し、WITH句を用いたサブクエリを使用しSETで変数を代入しようとしても、クエリは動きません。

クエリの例

DECLARE @hoge1 int
WITH TEMP as(
  SELECT hoge_id,
         count(hoge_val) as val
  FROM hoge_table
  GROUP BY hoge_id
)

SET @hoge = (  SELECT SUM(hoge_val)
                FROM TEMP
                GROUP BY hoge_id
                )

SELECT @hoge as test_val

なお、WITH句の位置を変えてみましたが、どの位置にしても動きませんでした。

テスト5 selectとwith句を用い、クエリから出てきた値を代入する(動く)

DRCLAREで変数を宣言し、WITH句を用いたサブクエリを使用しSELECTで変数を代入すると、クエリは動きました。

クエリの例

DECLARE @hoge1 int;
WITH TEMP as(
  SELECT hoge_id,
         count(hoge_val) as val
  FROM hoge_table
  GROUP BY hoge_id
)

SELECT @hoge = (  SELECT SUM(hoge_val)
                FROM TEMP
                GROUP BY hoge_id
                ); --セミコロンを取ると動かない

SELECT @hoge as test_val

テスト6 setとwith句を用い、クエリから出てきた値を複数代入する(動く)

DRCLAREで変数を宣言し、WITH句を用いたサブクエリを使用しSELECTで複数の変数に値を代入すると、クエリは動きました。

クエリの例

DECLARE @hoge1 int,--DECLAREを複数回使用すると動かない。
        @hoge2 int;--セミコロンを取ると動かない。

WITH TEMP as(
  SELECT hoge_id,
         count(hoge_val) as val
  FROM hoge_table
  GROUP BY hoge_id
)--WITH句の後にセミコロンを入れると動かない

SELECT @hoge = (  SELECT SUM(hoge_val)
                FROM TEMP
                GROUP BY hoge_id
                ),--カンマをセミコロンに変更したり、SELECTを2回使うと動かない
       @hoge = (  SELECT SUM(hoge_val)
                FROM TEMP
                GROUP BY hoge_id
                ); --セミコロンを取ると動かない

SELECT @hoge as test_val

直感的な説明ですが、DECLAREで1ブロック、WITH＆SELECTで1ブロック作成し、ブロックの間をセミコロンで分割しているのだと(勝手に)解釈しています。

※ きちんとした解説を見つからず、私もモヤモヤしています。

テスト7 with句で整数を代入し、fromで仮テーブルを指定する(動く)

With句内の仮テーブルに整数を代入し、本クエリのfromにて仮テーブルを指定します。
仮テーブルに代入した整数が列の値となって出力されます。

WITH TEMP_val as(
  SELECT count(hoge_val) as temp_val
  FROM hoge_table
)

SELECT hoge_table.hoge_val, TEMP_val.temp_val
FROM hoge_table, TEMP_val

今回のケースでは、temp_valが列となり、すべての行にTEMP_val.temp_valの値が表示されます。

おわりに

雑な記事ですが、誰かの役に立てばうれしいです。

参考リンク

SQL ServerのSELECTとSETで変数代入時の違い
 変数 (Transact-SQL) - SQL Server | Microsoft Docs
SQLServerでSELECT句で変数代入