How to Pass a Parameter to a Stored Procedure

There are 2 ways to pass a parameter to a stored procedure - a program command shape and a database profile – and each are done slightly differently. Passing a Parameter for a Stored Procedure in a Program Command shape When using a Program Command shape to execute a stored procedure you only need to write the stored procedure name within the Procedure Name field. Then if you would like to hard code a parameter into the stored procedure call, you would also add that to the Procedure Name field (figure 1). The stored procedure that is being used within these examples is:EXEC storedProcedureName @processName = ‘Process’.Yet, when executing a stored procedure with dynamic parameters, you only need to write the name of the stored procedure and add additional variables to the Variables field (figure 2). The parameters will be passed to the stored procedure in the order that the variables are declared. Adding a {#} to the Produce Name field is not needed.

Figure 1. A stored procedure that is passing a hard-coded parameter called ‘Process’.

Figure 2. Executing a stored procedure by calling the name and passing the parameters by adding them to the Variables window. The example includes a Dynamic Document Property called Process, but can be any variable.

Passing a Parameter for a Stored Procedure in a Database Profile

Another way to execute a stored procedure is by using a Database Profile. To pass a parameter for a stored procedure, you’ll first need to create the number of fields that corresponds to the number of parameters that will be passed. In the example below is there a stored procedure name followed by the parameter name being declared. The value that is passed into the parameter1 field will be written to the first question mark within the Stored Procedure field. Also, for this to work, a map needs to be created before the database connector shape. A value will populate the destination profile within the map, and the connector will evaluate the store procedure on a per document basis. Do not try to set the parameters under the parameter tab on the database connector’s configuration window. This configuration will not pass the values of the parameters to the stored procedure execution. The parameter values must be passed within the document that hits the connector shape.

Figure 3. Setting a stored procedure with parameters within a Database Profile. The order of the question marks within the Stored Procedure field corresponds to the order of the fields on the left.

Article originally posted at Boomi Community.