Working With Stored Procedure Parameters

Working with Stored Procedure Parameters There are four types of parameters that can be associated with stored procedures: ● ● ● ●

Input parameters, used to pass values to a stored procedure for processing. Output parameters, used by a stored procedure to pass return values to an application. Input/output parameters, used to pass values to a stored procedure for processing, and used by the stored procedure to pass return values to the application. A result parameter, used by some stored procedures to return an error or status value to an application. A stored procedure can only return one result parameter.

Whether a stored procedure uses a particular type of parameter depends both on the general language implementation of stored procedures on your database server and on a specific instance of a stored procedure. For any server, individual stored procedures may or may not use input parameters. On the other hand, some uses of parameters are server-specific. For example, on MS-SQL Server and Sybase stored procedures always return a result parameter, but the InterBase implementation of a stored procedure never returns a result parameter. Access to stored procedure parameters is provided by the Params property (in TStoredProc, TSQLStoredProc, TIBStoredProc) or the Parameters property (in TADOStoredProc). When you assign a value to the StoredProcName (or ProcedureName) property, the dataset automatically generates objects for each parameter of the stored procedure. For some datasets, if the stored procedure name is not specified until runtime, objects for each parameter must be programmatically created at that time. Not specifying the stored procedure and manually creating the TParam or TParameter objects allows a single dataset to be used with any number of available stored procedures. Note: Some stored procedures return a dataset in addition to output and result parameters. Applications can display dataset records in data-aware controls, but must separately process output and result parameters.

Setting up parameters at design time You can specify stored procedure parameter values at design time using the parameter collection editor. To display the parameter collection editor, click on the ellipsis button for the Params or Parameters property in the Object Inspector. Warning: You can assign values to input parameters by selecting them in the parameter collection editor and using the Object Inspector to set the Value property. However, do not change the names or data types for input parameters reported by the server. Otherwise, when you execute the stored procedure an exception is raised. Some servers do not report parameter names or data types. In these cases, you must set up the parameters manually using the parameter collection editor. Right click and choose Add to add parameters. For each parameter you add, you must fully describe the parameter. Even if you do not need to add any parameters, you should check the properties of individual parameter objects to ensure that they are correct. If the dataset has a Params property (TParam objects), the following properties must be correctly specified: ● ● ● ● ● ●

●

The Name property indicates the name of the parameter as it is defined by the stored procedure. The DataType property gives the data type for the parameter's value. When using TSQLStoredProc, some data types require additional information: The NumericScale property indicates the number of decimal places for numeric parameters. The Precision property indicates the total number of digits for numeric parameters. The Size property indicates the number of characters in string parameters. The ParamType property indicates the type of the selected parameter. This can be ptInput (for input parameters), ptOutput (for output parameters), ptInputOutput (for input/output parameters) or ptResult (for result parameters). The Value property specifies a value for the selected parameter. You can never set values for output and result parameters. These types of parameters have values set by the execution of the stored procedure. For input and input/output parameters, you can leave Value blank if your application supplies parameter values at runtime.

If the dataset uses a Parameters property (TParameter objects), the following properties must be correctly specified:

● ● ● ● ● ●

● ● ●

●

The Name property indicates the name of the parameter as it is defined by the stored procedure. The DataType property gives the data type for the parameter's value. For some data types, you must provide additional information: The NumericScale property indicates the number of decimal places for numeric parameters. The Precision property indicates the total number of digits for numeric parameters. The Size property indicates the number of characters in string parameters. The Direction property gives the type of the selected parameter. This can be pdInput (for input parameters), pdOutput (for output parameters), pdInputOutput (for input/output parameters) or pdReturnValue (for result parameters). The Attributes property controls the type of values the parameter will accept. Attributes may be set to a combination of psSigned, psNullable, and psLong. The Value property specifies a value for the selected parameter. Do not set values for output and result parameters. For input and input/output parameters, you can leave Value blank if your application supplies parameter values at runtime.

Using parameters at runtime With some datasets, if the name of the stored procedure is not specified until runtime, no TParam objects are automatically created for parameters and they must be created programmatically. This can be done using the TParam.Create method or the TParams.AddParam method: var P1, P2: TParam; begin ... with StoredProc1 do begin StoredProcName := 'GET_EMP_PROJ'; Params.Clear; P1 := TParam.Create(Params, ptInput); P2 := TParam.Create(Params, ptOutput); try Params[0].Name := 'EMP_NO'; Params[1].Name := 'PROJ_ID'; ParamByname('EMP_NO').AsSmallInt := 52; ExecProc; Edit1.Text := ParamByname('PROJ_ID').AsString; finally P1.Free; P2.Free; end; end; ... end;

Even if you do not need to add the individual parameter objects at runtime, you may want to access individual parameter objects to assign values to input parameters and to retrieve values from output parameters. You can use the dataset's ParamByName method to access individual parameters based on their names. For example, the following code sets the value of an input/output parameter, executes the stored procedure, and retrieves the returned value: with SQLStoredProc1 do begin ParamByName('IN_OUTVAR').AsInteger := 103; ExecProc; IntegerVar := ParamByName('IN_OUTVAR').AsInteger; end;

Preparing Stored Procedures As with query-type datasets, stored procedure-type datasets must be prepared before they execute the stored procedure. Preparing a stored procedure tells the data access layer and the database server to allocate resources for the stored procedure and to bind parameters. These operations can improve

performance. If you attempt to execute a stored procedure before preparing it, the dataset automatically prepares it for you, and then unprepares it after it executes. If you plan to execute a stored procedure a number of times, it is more efficient to explicitly prepare it by setting the Prepared property to True. MyProc.Prepared := True;

When you explicitly prepare the dataset, the resources allocated for executing the stored procedure are not freed until you set Prepared to False. Set the Prepared property to False if you want to ensure that the dataset is re-prepared before it executes (for example, if you change the parameters when using Oracle overloaded procedures).