【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!

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.

Adding Virtual Table

Select the All Tables folder and select Schema -> Create Virtual Table.

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.

The Virtual Table will be added with the OK button.

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.

Parameters

The stored procedure used this time requires only two Date type parameters, so delete all but the Date type templates (Para5 and Para6).

Change Code and Description of parameters Para5 and Para6 as follows.

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.

Code like this was generated.

The stored procedure used this time requires only two Date type parameters, so delete 1 to 4 out of the 6 parameters.

Change the remaining DateTime parameters name to @p1 and @p2 respectively and some other parts shown below as well.

Change the code to pass that parameter to the stored procedure.

like this

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.

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.

As soon as you specify the virtual table, you are presented with the list of parameters and selectable fields.

Enter values for parameters 1 and 2 to set all selectable fields as output items.

Try running the query with the Preview button.
Results are back.

A SQL statement like this was generated and executed.

I hope you find this article useful.
Thank you.