cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements

Community Helping Community

Invoke MSSQL stored procedure having OUT parameter from MSSQL job

332
Views
0
Helpful
0
Comments

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

CreatePlease to create content
Content for Community-Ad
FusionCharts will render here