Pubdbcall How to pass parameters to Stored procedure SQLserver

, ,
1

I appreciate help/direction from people who have done this. I tried calling a SQLserver stoed procedure with this call that is sanwiched between connect and close statements. Under the $data field of call statement, I created string parameters (by copying the fields from pipeline in) and mapped it to the pipeline-in fields. When ran the service I get error saying :

“com.wm.app.b2b.server.ServiceException: [ISS.0026.9103] No parameter named RequestNum”

Although I created this string under $data and also mapped into a input
field.

Any help appreciateD!!
Thanks KS

2

KS,
Did you use $dbProcSig ? If not ,map the procedure signature to this variable in the pub.db:call service.

3

Sorry but can you please explain how to map the signature… I didnt specify anything in $dbprocsig. I just created lots of strings beneath the $data representing all my parameters for the procedure and mapped pipelinein variables with these…

thanks,KS

4

Hi KS,

    $dbProcSig specifies data name,sqldata type,Direction(in or out)

parameters.I fyour data is not in order set $dbParamOrder is false.
$data is actual value for names you have specified in $dbProcSig.
$dbProc parameter is for schema.packageName.storedProcedureName.
Hope this will help you.

Thanks,
SriniK

5

Sorry KS,

 If it is distributed database specify catalog name in $dbCatalog.

Thanks,
SriniK

6

Again, how do I specify the multiple parameters specification in dbprocsig and corresponding values in $data?
thanks,
SK

7

Suppose you have two params empno and empname for the proc,
in $dbprocsig you need to hardcode it as follows
empno integer in
empname varchar in

in $data you will have to put
empno 1000
empname Tom

This should work. Revert to me if you don’t know how to hardcode the values in dbProcSig.

8

As you guessed, I need help hardcoding…
Although I want to try this after you explain , I still want to know if the hardcoding can be extended to passing values from pipeline in variables…

thanks for bearing with my basic questions…but these really help me,
KS

9

click on the call service which as you said is sandwiched between connect and close. Now on the pipeline tab on the lower Right hand window on Developer ,click on ‘set value’ icon (shown with a blue curved arrow) . This will give you a pop up box where you can set/hardcode the values by clicking on AddRow icon on the Top Right hand side corner.

10

Thanks for the suggestion Biju, it worked…!!!