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

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

このようなストアドプロシジャーを準備しました。

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

ちゃんと動いた！

では、このストアドプロシジャーをSharperlightから使用できるようにしてみます。

仮想テーブル

目的のストアドプロシジャーを仮想テーブルとしてデータモデルに追加します。

データモデルの編集

Sharperlightアプリケーションメニューからスタジオを起動します。
目的のストアドプロシジャーが定義されているデータベースと接続するデータモデルを開きます。
開いたら、テーブルページに移動します。

仮想テーブルの追加

All Tablesフォルダを選択し、Schema -> Create Virtual Tableを選択します。

SQL Stored Procedureオプションを選びます。
中央のフィールドにストアドプロシジャーを実行するコードを記入します。
コードと説明を入力します。
OKボタンで先に進みます。

OKボタンでVirtual Tableが追加されます。

追加された仮想テーブルをダブルクリックすることでフィールド詳細を中央の領域に表示できます。
出力用のフィールドが生成されたのと同時に、ストアドプロシジャーに渡すパラメータのテンプレートが６つ用意されます。

パラメータ

今回使用するストアドプロシジャーは、Date型のパラメータが２つだけ必要なので、Date型のテンプレート(Para5とPara6)以外は削除します。

パラメータPara5とPara6のCodeとDescriptionをそれぞれ以下のように変更します。

SQL Table Nameプロパティ

追加されたVirtual TableのSQL Table Nameプロパティを選択します。
ここにはストアドプロシジャー用のSharperlight独自のコードが自動生成されています。
そのコードを少し編集します。
右端の参照ボタンで生成されたコードをエディタに表示します。

このようなコードが生成されています。

今回使用するストアドプロシジャーではDate型のパラメータが２つのみ必要なので、６つのうちの１から４を削除します。

残ったDateTime型のパラメータ名を@p1、@p2とそれぞれ変更します。

そのパラメータをストアドプロシジャーに渡すようにコードを変更します。

このようにします。

これで完成です。

/*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フォルダに設定します。こうすることでクエリビルダから参照することができるようになります。

データモデルを保存します。

検証

早速、クエリビルダで追加した仮想テーブルの動作確認をしてみます。
クエリビルダを起動します。
フィルター領域では、製品に先ほど編集したデータモデル、テーブルには検索ダイアログから先ほど追加した仮想テーブルを指定します。

仮想テーブルを指定すると直ぐに、パラメータや選択可能なフィールド一覧が表示されます。

パラメータ1と2の値を入力し、全ての選択可能なフィールドを出力アイテムとして設定します。

プレビューボタンでクエリを実行してみます。
結果が戻ってきました。

このようなSQL文が生成されて実行されました。

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