Stored Procedure Parameter Retrieval Issue

[benlaan]

I have encountered an issue with using the TmsStoredProc component which is causing some problems with recently converted (from BDE) code.

We have many scenarios in which a stored procedure is defined within MS-SQL as having optional parameters, and since our application has never passed these to the calling stored procedure, these parameters have been executed as per their default values within the SQL. For example

Code: Select all

CREATE PROCEDURE dbo.VerifyClaim
    @ClaimNo      Integer,
    @VerifierID      Char(10),
    @Silent          Char(1) = 'N'
AS
    ...

You will see above that @Silent is default to 'N', and our executable never passes this value - it is only for testing purposes. This structure has worked successfully for several years using the BDE.

We have been using SDAC for all new work for a few years (quite happily) until a decision was finally made to move all the old BDE code to SDAC. Since we use our own component classes, we simply changed our ThslStoredProc from having ancestory of TStoredProc to TmsStoredProc. this has worked sucessfully except for default parameters as described above.

I have been able to determine that the problem is due to TmsStoredProc always calling sp_procedure_params_rowset prior to execution. The result is used to generate the parameter list which is used to construct the SQL command. It seems that this varies to the BDE which simply takes the parameters as recorded in the DFM and constructs the SQL from this list. Our DFM params list only lists the required parameters and the code only passes these to MS-SQL. The net effect is that instead of not passing these parameters, they are being passes as NULL, which is resulting in incorrect operation.

Code: Select all

in SDAC: 
    EXEC dbo.VerifyClaim @ClaimNo = 10, @VerifierID = 'ben', @Silent = NULL

in BDE:
    EXEC dbo.VerifyClaim @ClaimNo = 10, @VerifierID = 'ben'

As you can see, the two statements are not equivalent and can not be expected to perform the same.

NB: We have several thousand stored procedure components, of which several hundred are using optional parameters. We need a solution that will solve this problem without change to our code base or our stored procedures.

Any ideas?

Sincerely,
Ben Laan
HAMB Systems Ltd.

[Jackson]

Unfortunately this is SQL Server behaviour.
As you can see, OLE DB uses 'exec sp_procedure_params_rowset N'VerifyClaim', 1, NULL, NULL' query to get stored procedure parameters information.
This result set contains PARAMETER_HASDEFAULT and PARAMETER_DEFAULT columns, but for @Silent parameter these values are 0 and NULL.
SQL Server doesn't return actual information. Other ways to get information about parameter default value do not give results too.
We can suggest you workaround:
1. In design time select your TMSStoredProc component and click right mouse button on it.
2. In context menu select 'Convert to MSQuery'
3. Delete from stored procedure call query parameter with default value. For example instead of {:RETURN_VALUE = CALL VerifyClaim;1(:ClaimNo, :VerifierID, :Silent)} you will have {:RETURN_VALUE = CALL VerifyClaim;1(:ClaimNo, :VerifierID)}