SQLserverにて、WITH句を使用し変数に数字を代入する

ダッシュボードで進捗状況を可視化するために、SQLserverを使用する機会がありました。

変数に値を代入する際にエラーを頻発したので、成功例と失敗例をまとめていきたいと思います。

:::note warn
投稿内容は個人の見解であり、所属する組織の公式見解ではありません。
:::

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

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

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

## 様々なクエリのテスト
### テスト1 setで実数を代入する(動く)
DECLAREで変数を宣言し、SETで変数を指定します。クエリは動きます。

クエリの例

```sql
DECLARE @hoge int;
SET @hoge=5

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

クエリの例

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

SELECT @hoge as test_val
```

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

クエリの例

```sql
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で変数を代入しようとしても、**クエリは動きません。**

クエリの例

```sql
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で変数を代入すると、クエリは動きました。

クエリの例

```sql
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で複数の変数に値を代入すると、クエリは動きました。

クエリの例

```sql
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にて仮テーブルを指定します。
仮テーブルに代入した整数が列の値となって出力されます。

```sql
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で変数代入時の違い](https://lightgauge.net/database/sqlserver/select-set-caution)
[変数 (Transact-SQL) - SQL Server | Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-ver15)
[SQLServerでSELECT句で変数代入](https://it-engineer-info.com/database/4282/)