I have a stored procedure in Azure SQL DB that returns 2 output parameters, startDate
and endDate
based on an input date string.
This is the stored procedure:
CREATE PROCEDURE dbo.SP_DEDUCE_START_DATE_END_DATE
@inputDateTimeString NVARCHAR(MAX) NULL,
@startDate NVARCHAR(MAX) NULL OUT,
@endDate NVARCHAR(MAX) NULL OUT
AS
BEGIN
-- exact logic omitted as to demonstrate the 2 outputs date string only
SELECT
@inputDateTime AS inputDateTime,
@startDate AS startDate, @endDate AS endDate
RETURN;
END
GO
What I am looking for: a complete end-to-end solution to:
startDate
, endDate
from the stored procedureSTART_DATE
, END_DATE
(as I have other processing logic to affect the pipeline variables)What I have tried/viewed:
I would like to share my final end-to-end working solution.
Here is a simplified overview of my final pipelines: A lookup activity to trigger the SP; followed by setting 2 variables, then feed to downstream dataflow.
The syntax of parameters supplied to the SP:
You have to check Treat as null
option for the 2 outputs. Then supply the date string to SP.
To read the SP outputs and set the variables(only used START_DATE here as example since END_DATE is identical):
Put in @activity('SP_DEDUCE_START_DATE_END_DATE').output.firstRow.startdate
To feed the 2 variables to downstream dataflow:
Put in @variables('START_DATE')
in pipeline expression.