I am using TD .NET provider 12.x and have stored procedures. All my SPs have IN & OUT parameters. In my understanding, TD has a strange way to returning the OUT params as a dataset & it works fine. When I have SP that delete records from a table, and also returns OUT params.
When I execute this Sp from .NET, I get a empty dataset (tables = 0). On checking the data, it actually deletes the data correctly. But how can I know that records are deleted? I don't want to unnecessarily check the data for delete confirmation.
any help is appreciated. Thanks.
REPLACE STORED PROCEDURE spDelete ( IN pCodeToDelete INTEGER, OUT pStatus VARCHAR(10) ) BEGIN ... DELETE from db.Table WHERE code = :pCodeToDelete; .. pStatus = 'SUCCESS'; END;
.NET Data Provider returns Output Parameters as "Output Parameters". That is the TdCommand.Parameters collection must have one or more instances of TdParameter with Parameter.Direction set to ParameterDirection.Output. Of course these TdParameter instance corresponds to the SP definition. For example in the case above:
Note that in this case the CommandType is set to TEXT not StoredProcedure. Again this is a custom feature for ad-hoc query tools that cannot correctly set the CommandType per ADO.NET specification. This feature is compatible with TERADATA Session-Mode.
I cannot recreate the issue you mentioned. I will look into it if you can tell me how to recreate the issue.
I forgot to mention that this custom feature is documented in the help file; see "Returning Data of Output Parameters in a Result Set" in "Stored Procedure Dynamic Result Set" page.
This page also mentions: "If both Bound and Unbound output parameters are used in the call to the stored procedure a result set containing the data of the Unbound output parameters will NOT be returned."