【データモデル】ストアドプロシジャーを仮想テーブルとして定義してみる

今回は、ストアドプロシジャーを仮想テーブルとして使用する方法を試してみます。

このようなストアドプロシジャーを準備しました。
```
CREATE PROCEDURE QiitaSPTest   
    @DocumentDateStart date,   
    @DocumentDateEnd date   
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @dateFrom date;
	DECLARE @dateTo date;


	IF @DocumentDateStart = '' OR ISNULL(@DocumentDateStart,'')=''
		SET @dateFrom = '1900/01/01';
	ELSE
		SET @dateFrom = @DocumentDateStart;

	IF @DocumentDateEnd = '' OR ISNULL(@DocumentDateEnd,'')=''
		SET @dateTo = '2999/12/31';
	ELSE
		SET @dateTo = @DocumentDateEnd;

	SELECT 
		J1.CustomerName
		,J1.DocumentNumber
		,J1.DocumentDate
		,SUM(J0.Amount) as TotalAmount
	FROM [dbo].[SalesDetail] J0
	LEFT OUTER JOIN [dbo].[SalesHeader] J1 on J1.DocumentNumber = J0.DocumentNumber
	WHERE
		J1.DocumentDate BETWEEN @dateFrom and @dateTo
	GROUP By
		J1.CustomerName
		,J1.DocumentNumber
		,J1.DocumentDate
	ORDER BY
		J1.CustomerName
		,J1.DocumentNumber;
END	
GO
```
使用してみる。
```
DECLARE @Tmp TABLE (
	CustomerName NVARCHAR(200)
	,DocumentNumber int
	,DocumentDate date
	,TotalAmount int
)
INSERT INTO @Tmp (CustomerName, DocumentNumber, DocumentDate, TotalAmount)
EXEC QiitaSPTest '2023-04-01','2023-04-30'
SELECT * FROM @Tmp
```
ちゃんと動いた！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8a88f6b4-4859-90de-43d8-a944ed0c3cd1.png)
では、このストアドプロシジャーをSharperlightから使用できるようにしてみます。
## 仮想テーブル ##
目的のストアドプロシジャーを仮想テーブルとしてデータモデルに追加します。

### データモデルの編集 ###
Sharperlightアプリケーションメニューから**スタジオ**を起動します。
目的のストアドプロシジャーが定義されているデータベースと接続するデータモデルを開きます。
開いたら、`テーブル`ページに移動します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9be552f2-a07e-9c47-bca2-1d3a6f5d7c6d.png)

### 仮想テーブルの追加 ###
`All Tables`フォルダを選択し、**Schema** -> **Create Virtual Table**を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/df384725-c5c4-4a79-988d-c3123fe3d5ca.png)
`SQL Stored Procedure`オプションを選びます。
中央のフィールドにストアドプロシジャーを実行するコードを記入します。
`コード`と`説明`を入力します。
`OK`ボタンで先に進みます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/342658ab-d780-e608-fe48-038eba087e1f.png)
`OK`ボタンでVirtual Tableが追加されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cbd164e5-6235-82fc-9417-6473babd11c5.png)
追加された仮想テーブルをダブルクリックすることでフィールド詳細を中央の領域に表示できます。
出力用のフィールドが生成されたのと同時に、ストアドプロシジャーに渡すパラメータのテンプレートが６つ用意されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57882153-c137-3daf-a9be-cf100705b7d7.png)

#### パラメータ ####
今回使用するストアドプロシジャーは、Date型のパラメータが２つだけ必要なので、Date型のテンプレート(**Para5**と**Para6**)以外は削除します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6ea7637a-544d-7fee-4c41-57b00feb82ec.png)
パラメータ**Para5**と**Para6**の`Code`と`Description`をそれぞれ以下のように変更します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5fd4ef68-fa62-239d-c37c-017220b36faf.png)

#### SQL Table Nameプロパティ ####
追加されたVirtual Tableの`SQL Table Name`プロパティを選択します。
ここにはストアドプロシジャー用のSharperlight独自のコードが自動生成されています。
そのコードを少し編集します。
右端の参照ボタンで生成されたコードをエディタに表示します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/de0d3d3d-6504-0447-1a40-8b8a22be859b.png)
このようなコードが生成されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/71e61e16-7397-22e3-f22a-464ece0033fd.png)
今回使用するストアドプロシジャーではDate型のパラメータが２つのみ必要なので、６つのうちの１から４を削除します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/322d942e-4b02-adfd-d596-e49e295afb52.png)
残ったDateTime型のパラメータ名を`@p1`、`@p2`とそれぞれ変更します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3f6e9752-e5e4-31b5-ac1b-8c058b31514a.png)
そのパラメータをストアドプロシジャーに渡すようにコードを変更します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e3203c9d-1f63-216b-407d-e288687654c1.png)
このようにします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d6d939cc-117a-3a2c-fec6-c625ab65e52f.png)
これで完成です。
```
/*MoveSQLBlockToTopBegin*/
/*These parameters can be used passed into the stored procedure below replacing any constants*/
declare @p1 datetime =##IF( {?Filter[1]./Param1.Operator}="All" ) '1901-01-01' ##ELSE '{?Filter[1]./Param1[1]}'##ENDIF
declare @p2 datetime =##IF( {?Filter[1]./Param2.Operator}="All" ) '2999-01-01' ##ELSE '{?Filter[1]./Param2[1]}'##ENDIF

declare @Temp_SP1 table (
    [CustomerName] nvarchar(max),
    [DocumentNumber] integer,
    [DocumentDate] datetime,
    [TotalAmount] integer
)
insert into @Temp_SP1 ([CustomerName],[DocumentNumber],[DocumentDate],[TotalAmount])
EXEC QiitaSPTest @p1, @p2
/*MoveSQLBlockToTopEnd*/

@Temp_SP1
```
`OK`ボタンで`SQL Table Name`プロパティの変更内容を保存します。

最後に追加/編集したVirtual TableをTable Displayフォルダに設定します。こうすることでクエリビルダから参照することができるようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2bc50b0b-9c8a-617f-c8b3-2d85ae3ce477.png)
データモデルを保存します。

## 検証 ##
早速、クエリビルダで追加した仮想テーブルの動作確認をしてみます。
クエリビルダを起動します。
フィルター領域では、`製品`に先ほど編集したデータモデル、`テーブル`には検索ダイアログから先ほど追加した仮想テーブルを指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57047fbf-2f64-9a1b-5632-e953409faa1a.png)
仮想テーブルを指定すると直ぐに、パラメータや選択可能なフィールド一覧が表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/933e48b9-7520-e79e-14b6-ed652892c277.png)
パラメータ1と2の値を入力し、全ての選択可能なフィールドを出力アイテムとして設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c870ba01-b8fd-1af0-cd55-487c86065672.png)
`プレビュー`ボタンでクエリを実行してみます。
結果が戻ってきました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/33694e02-3fa5-6694-0b4c-d4b1addbeca9.png)
このようなSQL文が生成されて実行されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/69f2a2f0-ae64-8d8b-ff5d-b28878a7238a.png)

この記事が何かのお役に立てれば幸いです。
では失礼します。