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

How to capture output of SQL Stored Procedure in variable

Anonymous — Thu, 10 May 2018 10:20:54 GMT

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

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

Anonymous — Thu, 10 May 2018 11:22:26 GMT

This should help....

https://help.talend.com/reader/iYcvdknuprDzYycT3WRU8w/Y4ldtMoTiuC0d9L2HNRqzw

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

Anonymous — Thu, 10 May 2018 12:31:42 GMT

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

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

Anonymous — Fri, 11 May 2018 09:00:48 GMT

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

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

Anonymous — Wed, 16 May 2018 06:38:16 GMT

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

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

Anonymous — Wed, 16 May 2018 09:46:26 GMT

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

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

Anonymous — Wed, 16 May 2018 12:50:24 GMT

HI,

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

Thanks

Amit

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

Anonymous — Wed, 16 May 2018 14:17:40 GMT

Your procedure will need an output parameter.

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

Anonymous — Thu, 17 May 2018 06:57:33 GMT

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

1.1. tMSSQLSP

- Opted Output as a Output variable as shown below

1.2 tLogRow1

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