I’m trying to pass parameters into a stored procedure in a Main Workflow and i am receiving an error.
I’m sure I’m doing something incorrect, but I’m not sure what it’s asking me to do and why.
Any help is appreciated.
To be able to create the return data pattern, Service Connect needs to run the statement as you exit this editor. So in the top box it’s asking for a real value in place of the dynamic link it can use for that.
Put a real part number in there and you should be fine.
Thanks for your help Dary. Actually i tried with hard code its working fine.
But i have a csv file with multiple records, based on csv file i would like to dynamically pass the value to stored procedure and save the result to another output file.
So how can i go ahead.
The dynamic link should work fine for you.
You only need to replace that link with a real example in the final stage of setting up, as shown in the first of your screenshots. That lets it run once to create the output schema. You can leave the dynamic link in place in the main set-up screen and it can vary whenever it runs afterwards.
Daryl , i replace the dynamic link with real value, after that it generating the output file and showing the stored procedure output, but when i am passing the csv file to input channel folder, its not vary the output. Its only showing same result.
I don’t know where i have to link the input schema column to stored procedure as a input parameter.
I may be missing the detail of what you’re doing. Can you show screenshots of the other parts of this SQL widget and what you’re doing with it?
I may not be able to help too much because I’m not at my computer, but I’ll say if I spot anything.
I will again share you my requirement.
I have to create a workflow which can take the csv file as a input and
i have to execute a stored procedure(DB Operation) inside the service connect.
which will take the csv column as a input parameter and get the result from SQL.
then service connect will generate the result as a another xml file,
which i will use later
for another process.
please see below workflow complete process
I will probably need to check against some of my working workflows to be properly helpful, I’m afraid.
But a couple of extra questions: are you checking the document tracking, and if so where and what is the error? And is your input to the DB query always a single row?
Yah I have checked the document tracking there is no error.
I have attached snap shot of document tracking for your reference.
My query has multiple row result set.
Sorry, I’ve been viewing on my phone and it’s difficult to see properly. I might be wrong, but I don’t know that the DB operation can take multiple rows directly. I would expect to need to put it in a loop for that, or at least to put a conversion before it with a for-each connection.
Does it work as expected if you put a file with a single row through the workflow?
When i was pass a real time value to stored procedure. It working fine. Its showing single and multiple rows also.
Only when i removed the real time value and i passed the dynamic value, the result is not coming but i have seen in document tracking its successfully.
snap shot for dynamic value:
Result of output file:
i know i am missing something.
how to pass the dynamic value or how the csv file take a input parameter to stored procedure?
OK, there are two things here that might be the problem, depending on whether I understand where you’ve got to or not.
The first is passing the parameter. Going back to your first question in this thread, you show two screenshots. The second screenshot is 100% correct as far as I can tell. The first screenshot, with the statement at the top asking you to replace the dynamic link, is where you need to put a real PartNum - but when you do that, don’t change the other SQL statement, leave that with the dynamic link as it is. As far as I know, this DB operation will then behave correctly, using the real PartNum value to create the schema, and afterwards using the dynamic link whenever the operation is called.
The second is the problem of multiple parameter values. My workflows are all set up to take a single parameter, though sometimes the queries themselves return multiple rows to be used. Your workflow is currently set up that way too, so I think the DB operation will only work once for the first row of whatever set you’re passing in. I don’t think you can expect it to return a sequence of results for a sequence of parameters unless the workflow as a whole is triggered once for each one.
I note your document tracking is not actually showing any errors, so it’s not failing as far as you’ve shown.
I’d like to test for myself whether you can make it work for multiple inputs without a subworkflow loop, but I won’t have the chance until next week when I’m back in the office.
Thank you Daryl, i made the changes suggested by you in step1.
Regarding second problem i did some changes, i created a sub and main workflow. Now its working fine.
Once again Thank you Daryl.
1 Like