Calling SQL Stored Procedures
How to call SQL Stored Procedures from Sharperlight.
In this example filter parameters are passed to a stored procedure that returns a dataset which is used for reporting. These same basic steps can be used in Studio when developing a Datamodel.
Please note that there is now a SQL Stored Procedure Mode when creating tables over Stored Procedures that helps to automate many of the steps shown in this video. Please see screen shots below.
[NEWCHANGESTART]
Example Stored Procedure
CREATE PROCEDURE MyCustomSP @staging-sharperlight-com.stackstaging.comAccCode nvarchar(100),@staging-sharperlight-com.stackstaging.comCreatedDateFrom datetime,@staging-sharperlight-com.stackstaging.comCreatedDateTo datetime
AS
BEGIN
SET NOCOUNT ON:
select AcctCode,AcctName,CreateDate from dbo.[OACT]
where (AcctCode = @staging-sharperlight-com.stackstaging.comAccCode or @staging-sharperlight-com.stackstaging.comAccCode =“ or@staging-sharperlight-com.stackstaging.comAccCode IS NULL)
and CreateDate between @staging-sharperlight-com.stackstaging.comCreatedDateFrom and @staging-sharperlight-com.stackstaging.comCreatedDateTo
END
GO
exec MyCustomSp “,‘2001-01-01’,‘2014-12-31’
exec MyCustomSp ‘100011’,‘2006-01-01’,‘2014-01-01’
exec MyCustomSp “,‘2012-01-01’,‘2014-12-31’
DECLARE @staging-sharperlight-com.stackstaging.comTmp TABLE (AccCode NVARCHAR(100) NULL UNIQUE, AccName NVARCHAR(100), CreatedDate DATETIME)
INSERT INTO @staging-sharperlight-com.stackstaging.comTmp (AccCode, AccName, CreatedDate)
EXEC SBODemoAU.dbo.MyCustomSp “,‘2001-01-01’,‘2014-12-31’
SELECT * FROM @staging-sharperlight-com.stackstaging.comTmp
SQL Produced by Sharperlight
/********************************************
RESOLVED QUERY Table:CT24538
Process Time: 00:00:00:001
SQL Execution Time: 00:00:00:058
Get Data Time: 00:00:00:058
********************************************/
DECLARE @staging-sharperlight-com.stackstaging.comTmp TABLE (AccCode NVARCHAR(100) NULL UNIQUE AccName NVARCHER(100), CreatedDate DATETIME)
INSERT INTO @staging-sharperlight-com.stackstaging.comTmp (AccCode, AccName, CreatedDate)
EXEC [SBODemoGB].[dbo].MyCustomSp
‘100000’
,‘2005-01-28 00:00:00.000‘,‘2015-01-28 23:59:59.998’
SELECT
TOP 20000
J001.[AccCode]
,J001.[AccName]
,DATEADD(DD,0,DATEDIFF(DD,0,J001.[CreatedDate]))
FROM
@staging-sharperlight-com.stackstaging.comTmp J001
WHERE
J001.[AccCode]=N’100000′
AND j001.[CreatedDate] BETWEEN ‘20050128 00:00:00.000’ AND ‘20150128 23:59:59.998’
AND J001.[AccName] LIKE N’C%’
GROUP BY
J001.[AccCode]
,J001.[AccName]
,DATEADD(DD,0,DATEDIFF(DD,0.J001.[CreatedDate]))
ORDER BY
1,2,3
/********************************************
UNRESOLVED QUERY
SELECT
TOP 20000
J001.[AccCode]
,J001.[AccName]
,DATEADD(DD,0,DATEDIFF(DD,0,J001.[CreatedDate]))
FROM
/*MoveSQLBlockToTopBegin*/
DECLARE @staging-sharperlight-com.stackstaging.comTmp TABLE (AccCode NVARHCAR(100) NULL UNIQUE AccName NVARHCHAR(100), CreatedDate DATETIME)
INSERT INTO @staging-sharperlight-com.stackstaging.comTmp (AccCode, AccName,CreatedDate)
EXEC {=DBS}.MyCustomSp
##IF({?Filter[1]./AccCode[1].Operator}=”All”)
“
##ELSE
{?Filter[1]./AccCode[1]”}
##ENDIF
##IF({?Filter[1]./CreatedDate[1].Operator}=”All”)
,‘1900-01-01′,’2020-12-31’
##ELSE
,‘{?Filter[1]./CreatedDate[1]}’,'{?Filter[1]./CreatedDate[2]}’
##ENDIF
/*MoveSQLBlockToTopEnd*/
@staging-sharperlight-com.stackstaging.comTmp J001
WHERE
J001.[AccCode]=N’100000′
AND J001.[CreatedDate] BETWEEN ‘20050128 00:00:00.000’ AND ‘20150128 23:59:59.998’
AND J001.[AccName] LIKE N’C%’
GROUP BY
J001.[AccCode]
,J001.[AccName]
,DATEADD(DD,0,DATEDIFF(DD,0,J001.[CreatedDate]))
ORDER BY
1,2,3
Create a Virtual Table over a SQL Stored Procedure
After creating the Virtual Table you will be prompted to edit the parameters
Once the Table is created you can add it to the Table Display and also edit the Filter parameters shown in blue to suit the Stored Procedure
Edit the Table SQL Name property logic to best suit the parameters required by the Stored Procedure. Notice that six auto generated filter parameter are present to help assist with the process. In most cases these would be modfied to suit the description and data type required by the Stored Procedure.
Shows the final result of the Virtual Table with the six default filter parameters
The Query Builder results with the SQL generated by the engine. Noticed that @staging-sharperlight-com.stackstaging.comp1 is set to whatever the filter value was for Parameter 1 and this is then passed to the Stored Procedure
