You are viewing limited content. For full access, please sign in.

Question

Question

Using a stored procedure in workflow and using the return value

Workflow How To Version 10
Updated December 11, 2020
asked on December 10, 2020

Created a stored procedure that works to create and increment file number counter that we would like to use in a metadata field.  I am using a Custom SQL Query for this:

After this query, there is an Assign Token Values and when trying to use the returned value from the custom query, the only options available are Results Found and Results Count. 

The stored procedure return a result of the file number we want and in the desired format in SQL Management Studio, but this is not available for selection in the Assign Token tool.  How can I make the result available?

 

0 0

Replies

replied on December 11, 2020 • Show version history

Hello Steven,

Have you tried select the result from within the same SQL Procedure and not from the workflow designer?

In other terms,

  • declare all your variables inside the procedure from SQL
  • write the select query that returns your desired set of values.
  • in Laserfiche Workflow's custom query, Use exec [dbo].[GenerateFileNumber] only and provide the parameters from within the parameters section. These parameters can be either static or brought from earlier activities as tokens.

 

In your next activity, you must have your select query results as tokens for your to use. 

Ps: I don't think you need quotations for your parameters in the exec statement.  I personally don't use it usually.

Hope this answers your question.

Joseph

0 0
View 1 previous reply
replied on December 11, 2020

Hi Joseph, 

The stored procedure has everything that you suggest within it along with a select statement at the end to return what the file number should be.  I removed the variables and the custom query is now only set to be EXEC GenerateFileNumber 'B'.  The parameter of 'B' is a doctype code for which file number to use.  I run a test and get a 1 row(s) affected result and my table has the number incremented by 1.

You said "In your next activity", I have an Assign Tokens Values activity and the only options that are showing from the Custom Query are Results Found, Results Count and what looks to be a blank line.  The Results Found gives a boolean response of True and not what is returned from the procedure (the file number correctly formatted). 

What am I doing that is not allowing me access to the results from the stored procedure?

 

Thanks!

0 0
replied on December 11, 2020

Hello Steven,

I dont think i can provide the necessary help if i'm not seeing your procedure and workflow activities. 

so i made a small example on my own machine.

workflow:

assign token value activity:

procedure:

 

Make sure if you are selecting multiple values in your custom query to insert a for each row activity to iterate through your returned values by row.

Hope it helps.

Joseph

0 0
replied on December 11, 2020

Hi Joseph, 

Thanks for your responses.  You helped with cleaning up the actual command used in the Custom Query activity.  

As I mentioned, the Assign Tokens activity had three items to choose from and they were Results Found, Results Count and a Blank line.  I selected the blank line and this was the actual formatted result that I wanted.  To fix it from showing a blank line, I added "AS FileNumber" to the end of the last Select statement in the Stored Procedure.  Now it gives the "FileNumber" as an option in the Assign Token activity for the Custom Query Results.

0 0
replied on December 11, 2020

Glad I provided the slightest help. 

Cheers!  

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...