How to capture output of SQL Stored Procedure in variable

Six Stars

How to capture output of SQL Stored Procedure in variable

Hi, 

 

I have to call SQL Sp from talend and capture its result set in a variable and this variable will be used further.

 

Please let me know how to do the same.

 

I am newbie to the Talend, please help me on the same.

 

Thanks

Amit

Sixteen Stars

Re: How to capture output of SQL Stored Procedure in variable

Six Stars

Re: How to capture output of SQL Stored Procedure in variable

Hi rhall, Thanks for the reply.

I already gone through the detail which you have shared with me however still i am not able to achieve the feet.

 

Could you please share detail with example, step by step would be fruit on tree.

 

Many Thanks in advance for your support and help!!

 

Thanks

Amit Srivastava

Tags (1)
Moderator

Re: How to capture output of SQL Stored Procedure in variable

Hello,

You can use a tParseRecordSet behind a tMySqlSP.

Could you please refer to this scenario about:TalendHelpCenter: Scenario: Retrieving personal information using a stored procedure to see if it is what you are looking for?

Best regards

Sabrina

 

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: How to capture output of SQL Stored Procedure in variable

Hi xdshi,

Thanks for the post.

As i am newbie, i do not have that understanding, let me show you my requirement again, a bit in more detail

 

- I have Stored Procedure that return result, defination of SP is

 

Create PROCEDURE dbo.GetCount
AS
BEGIN
SET NOCOUNT ON

DECLARE @NewSeqValue INT
SET @NewSeqValue = SELECT Count(*) from dbo.DataLoad(Nolock)
SELECT @NewSeqValue AS Count

END

 

Now, i want to execute this SP via Talend and want to capture result in another variable in talend, so that i can use in further in Talend flow.

 

Please suggest, step by step great would be great!!

Thanks

Amit Srivastava

 

 

Sixteen Stars

Re: How to capture output of SQL Stored Procedure in variable

Which database is your procedure running on? Also, it doesn't look like it is set to return an output parameter.

Six Stars

Re: How to capture output of SQL Stored Procedure in variable

HI,

I am using SQL Server 2008 R2, also it returning result of counts.

 

Thanks

Amit

Sixteen Stars

Re: How to capture output of SQL Stored Procedure in variable

Your procedure will need an output parameter.

Six Stars

Re: How to capture output of SQL Stored Procedure in variable

Hi Rhall,

I have included OutPut parameter in my sp as below

Stored Procedure

 Create PROCEDURE dbo.GetCount
@OutPut INT OUTPUT
AS
BEGIN
SET NOCOUNT ON

DECLARE @SeqValue INT
SET @SeqValue = (SELECT Count(*) from dbo.test(Nolock))
SELECT @SeqValue AS Count

END

 

-- On executing SP manually, its giving output as 9

 

1. OverAllFlow

    OverAllFlow.JPG

 

1.1. tMSSQLSP

 - Opted Output as a Output variable as shown below

    tMSSQLSP.JPG

1.2  tLogRow1

 

LogOutPut.JPG

 

Issue - When I am executing this SP via Talend and trying to capture value in Output variable (Snapshot above), then it showing value as 0 in tLogRow, expected result to be 9

 

Please suggest, what i am doing wrong in this implementation.

 

Also, i have tested to include insert statement in above SP and executed the SP via Talend, record inserted in respected table but output not captured in OutPut variable which means SP is calling properly via talend only issue is with OutPut caprturing.

 

Please suggest!!

 

Amit Srivastava