【DataModel】Using a stored procedure as a virtual table

This time, I will try to use a stored procedure as a virtual table.

I prepared a stored procedure like this.
```
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
```
Try it...
```
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
```
It worked!
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8a88f6b4-4859-90de-43d8-a944ed0c3cd1.png)
Now let's make this stored procedure available from Sharperlight.
## Virtual Table ##
Add the desired stored procedure as a virtual table to the datamodel.
### Editing the datamodel ###
Launch **Studio** from the Sharperlight application menu.
Open the datamodel that connects to the database where the desired stored procedure is defined.
Once open, go to the `Table` page.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9be552f2-a07e-9c47-bca2-1d3a6f5d7c6d.png)

### Adding Virtual Table ###
Select the `All Tables` folder and select **Schema** -> **Create Virtual Table**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/df384725-c5c4-4a79-988d-c3123fe3d5ca.png)
Select `SQL Stored Procedure` option.
Write the execution code for the stored procedure in the middle field.
Give its code and description.
Then click `OK` to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5c887aed-4d5c-07b1-b835-90bdd0f32df8.png)
The Virtual Table will be added with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cbd164e5-6235-82fc-9417-6473babd11c5.png)
Double-click to display field details in the central area.
At the same time the fields for output are generated, six templates of parameters to pass to the stored procedure are prepared.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57882153-c137-3daf-a9be-cf100705b7d7.png)

#### Parameters ####
The stored procedure used this time requires only two Date type parameters, so delete all but the Date type templates (**Para5** and **Para6**).
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6ea7637a-544d-7fee-4c41-57b00feb82ec.png)
Change `Code` and `Description` of parameters **Para5** and **Para6** as follows.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5fd4ef68-fa62-239d-c37c-017220b36faf.png)

#### SQL Table Name property ####
Select the `SQL Table Name` property of the added Virtual Table.
The Sharperlight-specific code for stored procedures is auto-generated here.
Edit that code a bit.
View the generated code in the editor with the browse button on the far right.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/de0d3d3d-6504-0447-1a40-8b8a22be859b.png)
Code like this was generated.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/71e61e16-7397-22e3-f22a-464ece0033fd.png)
The stored procedure used this time requires only two Date type parameters, so delete 1 to 4 out of the 6 parameters.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/322d942e-4b02-adfd-d596-e49e295afb52.png)
Change the remaining DateTime parameters name to `@p1` and `@p2` respectively and some other parts shown below as well.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3f6e9752-e5e4-31b5-ac1b-8c058b31514a.png)
Change the code to pass that parameter to the stored procedure.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e3203c9d-1f63-216b-407d-e288687654c1.png)
like this
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d6d939cc-117a-3a2c-fec6-c625ab65e52f.png)
That's all.
```
/*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
```
Save the changes to the `SQL Table Name` property with the `OK` button.

Set the last added/edited Virtual Table to the Table Display folder. By doing this, you will be able to refer to it from the query builder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2bc50b0b-9c8a-617f-c8b3-2d85ae3ce477.png)
Save your datamodel.

## Verification ##
Now, let's check the operation of the virtual table added with the query builder.
Start Query Builder.
In the filter area, specify the data model you just edited for `Product` and the virtual table you just added from the search dialog for `Table`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b668cdfd-c4e0-2029-5e1a-da5016ecf9d7.png)
As soon as you specify the virtual table, you are presented with the list of parameters and selectable fields.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/901aefe7-8cc2-57e9-df48-543100fe2afd.png)
Enter values for parameters 1 and 2 to set all selectable fields as output items.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/17a382d3-3a37-126c-11d6-960dcca5a3dd.png)
Try running the query with the `Preview` button.
Results are back.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e4f6df4a-7bda-d6f5-366d-45f211d6cba8.png)
A SQL statement like this was generated and executed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/69f2a2f0-ae64-8d8b-ff5d-b28878a7238a.png)

I hope you find this article useful.
Thank you.