ダッシュボードで進捗状況を可視化するために、SQLserverを使用する機会がありました。
変数に値を代入する際にエラーを頻発したので、成功例と失敗例をまとめていきたいと思います。
投稿内容は個人の見解であり、所属する組織の公式見解ではありません。
本記事の目的
本来であれば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句で変数代入