Workflow: Get results of stored procedure from OUTPUT parameter

replied on August 4, 2023 • Show version history

Thanks for the responses. I did try putting a select at the end of the procedure, but it doesn't look like it's working...

I clicked on the Test and got a one-record result, then tried to create a token for the @ResponseText that I'm expecting should be returned, but there's no option for it yet.

UPDATE: I had to give an alias name to the @ResponseText; now it works. Thanks!

1 0

replied on August 3, 2023 • Show version history

Mike,

The last line of your stored procedure should be a SELECT statement. Whatever columns are in the SELECT statement will be returned as tokens to workflow via the custom query.

0 0

replied on August 3, 2023

Do you have an example of what your stored procedure looks like? I didn't have to do the last select statement or set a parameter like in your screenshot and was able to pull in the tokens.

0 0

replied on August 3, 2023 • Show version history

Here's the full stored procedure with URL edited for obfuscation:

USE [DatebaseName]
GO

/****** Object: StoredProcedure [dbo].[spPutVoucherEEExpenseE1] Script Date: 8/2/2023 3:13:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter procedure [dbo].[spPutVoucherEEExpenseE1] @SupplierNumber as varchar(8), @InvoiceNumber as varchar(40), @GrossAmt as varchar(40),
@Account as varchar(8000), @AccountAmt as varchar(8000) , @InvoiceDate as varchar(12), @ResponseText as varchar(8000) OUT as

DECLARE @URL NVARCHAR(MAX) = 'http://e1server:9192/jderest/orchestrator/TA_FA_AP03_ORC_AddVoucher';
DECLARE @Object AS INT;
DECLARE @Body AS VARCHAR(8000) =
'{'
if Len(@SupplierNumber) > 0
begin
SET @Body = @Body + '"Company": "00001", '
SET @Body = @Body + '"Long_Address_Number": "'+@SupplierNumber+'",'
end
if Len(@InvoiceNumber) > 0
begin
SET @Body = @Body + '"Invoice_Number": "'+@InvoiceNumber+'",'
end
if Len(@InvoiceDate) > 0
begin
SET @Body = @Body + '"Invoice_Date": "'+@InvoiceDate+'",'
end
if Len(@InvoiceDate) > 0
begin
SET @Body = @Body + '"G_L_Date": "'+@InvoiceDate+'",'
end
if Len(@GrossAmt) > 0
begin
SET @Body = @Body + '"GridData_1":[{"Gross__Amount": "'+@GrossAmt+'",'
SET @Body = @Body + '"Remark": "Expense Reimbursement" }], '
end

if Len( PATINDEX('%|%', @Account))>0
begin
SET @Body = @Body +'"GridData_GL": ['

end
while PATINDEX('%|%', @Account)>0
begin
SET @Body = @Body +'{
"Account_Number": "'+left(@Account, PATINDEX('%|%', @Account)-1)+'",
"Amount": "'+left(@AccountAmt, PATINDEX('%|%', @AccountAmt)-1)+'"
},
   '
   Select @Account = right(@Account,Len(@Account)-PATINDEX('%|%', @Account))
   Select @AccountAmt = right(@AccountAmt,Len(@AccountAmt)-PATINDEX('%|%', @AccountAmt))

end

SET @Body = Left(@Body,Len(@Body)-4) + '
]}'

select @Body

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post', @URL, 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Authorization', 'Basic redacted'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'environment', 'redacted'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'role', '*All'
EXEC sp_OAMethod @Object, 'send', null, @body
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OADestroy @Object

GO

0 0

View 1 previous reply

SELECTED ANSWER

replied on August 3, 2023

Mike,

Change your final SELECT statement in your stored procedure to something like

SELECT @Body AS [Body]

When you test the custom query in workflow you will be prompted to enter the parameters and will get a response dialog back similar to this;

You should then be able to see the tokens returned from the custom query. Something like this;

1 0

replied on August 3, 2023

Just echoing what Cliff said. You are missing a select statement at the bottom of your script, probably resulting in a blank token name.

0 0

replied on August 4, 2023

Thanks Cliff....I had already added a Select for the @ResponseText output but wasn't getting anything until I added an alias name for it as well.

Now it's working.

0 0

replied on August 9, 2023

Your screenshot below actually shows it in the list as an extra blank row after Results Found and Result Count. I'll add a request to automatically name unnamed params and we'll see what we can do to make it better.

2 0

Question

Question

Workflow: Get results of stored procedure from OUTPUT parameter

Answer

Replies

Sign in to reply to this post.