Visual StudioのDataSet、クエリー設定でCase文＋パラメータでエラーが出る

自分用メモ。詳細についてはまだ精査していないので突っ込みはご容赦を……。

研修課題用のプログラム、C#、Visual Studioで型付きデータセットを作る際に設定するクエリーに、CASE文と、CASE文中に「＠」から始まるパラメーターを設定して登録しようとしたところ、下記のようなエラーがでてしまいました。

スカラー変数 "@param" を宣言してください。

下記が問題のSQL。SQL Server Management Studioからだと動きますが、Visual Studioのクエリーデザイナーからだと通りません。

SELECT 
    Col1
    Col2
    Col3
FROM Data
WHERE DateCol BETWEEN CASE datepart(weekday, @param )
        WHEN 7
            THEN (DATEADD(day, 9 - (datepart(weekday, @param )), @param ))
            ELSE (DATEADD(day, - (datepart(weekday, @param ) - 2), @param ))
        END
        AND CASE datepart(weekday, @param)
        WHEN 7
            THEN (DATEADD(day, (13 - datepart(weekday, @param )), @param ))
            ELSE (DATEADD(day, (6 - datepart(weekday, @param )), @param ))
        END;

何をしたいかというと、「今日を含む月曜～金曜のデータを取得する」という条件文をつくりたいというもの。ただし1点やっかいなのは、「土曜日は翌週に含める」というもの。曜日に番号を振ったものを取得するDatePartは、下記のような戻りを返します。

土曜: 7
日曜: 1
月曜: 2
火曜: 3
水曜: 4
木曜: 5
金曜: 6

上記のSQLだと、土曜日の場合とそれ以外で処理を変える必要があり、そこにCASE文を使ってみたのですが……Visual StudioのクエリビルダーはこのCASE文中のパラメーターを認識できないようです。仮にこの「Between A and B」のBやAだけをパラメーターに変えると問題無く通ります……。なるほどCASE文中のパラメーターはFillメソッドなどの引数として抽出できないのね……。

とりあえず埒があかないので、詳細な原因を突き止めるのを断念して曜日番号を以下のように変換する方向で検討しています。

土曜: 7 → 1
日曜: 1 → 2
月曜: 2 → 3
火曜: 3 → 4
水曜: 4 → 5
木曜: 5 → 6
金曜: 6 → 7

これは、以下の式でなんとかなりそうです。

DATEPART(weekday, @param) % 7 + 1

曜日番号を7で割った剰余を取れば、7の場合だけ1、それ以外は1～6の数字になるので、上記の変換を実現することができます。これを使えば、前述のSQLはCASE文を使わずに下記のように書き換えられます。型変換とかは不要っぽかったので端折ってます。

SELECT 
    Col1
    Col2
    Col3
FROM Data
WHERE DateCol
BETWEEN
  DATEADD(day, - (DATEPART(weekday, @param) % 7 + 1 - 2), @param)
AND
  DATEADD(day, 5 - DATEPART(weekday, @param) % 7 + 1, @param);

とりあえず、デザイナーを使う状況ではあまり複雑なSQLは組まない方が良さそうですね。というか、このタイプの「お手軽プログラム」機能は、やはりお手軽な状況以外、業務では制約が多くて使い勝手が悪いのが難点。