Introduction
This document describes how to call MSSQL stored procedures having output parameter from an MSSQL job
Pre-requisites
1. MSSQL database server that hosts the given stored procedure to be invoked.
2. Licensed MSSQL adapter installed on the Tidal master
(we had SQL Server 2008 R2 as the database server, MSSQL adapter version 2.1.0.458, TES 6.1)
<!--break-->
Creating Tidal job
Create an MSSQL job. After setting the required details in the Run tab, select job type as SQL job in the MSSQL tab and select the required database on which the stored procedure is deployed
If the stored procedure has an output(OUT) parameter called outparm declared, then write the query within the job as given below. In this example, the input parameters are EmpId and EffectiveDate. Output parameter is outparam
Declare @outparm int
EXEC [dbo].[MyStoreProc]
@EmpId = 16 ,
@EffectiveDate = '2014-07-31' ,
@outparm= @outparm OUTPUT
Select @outparm
This will display the value of @outparm in the Tidal job output. you could configure the job to succeed or fail based on the @outparm value
