At 12:30 PM 21-02-01 +0000, you wrote:

>Hi Friends,
>
>I need a help in Converting form MSSQL to IB6.
>How to get the Result and Output parametes of MSSql
>Stored Procedure in IB.

Every RDBMS has its own procedure language. You won't be able to use your MSSQL procedure code in InterBase. IB has some powerful tricks that others don't have.

IB doesn't have the equivalent of MSSQL's Result - you can pass a result back if you want one, using an output parameter.

If you want just one "row" of output from a SP, declare your output arguments in the CREATE PROCEDURE statement. Treat them as variables inside the procedure and place the values you want into those variables. Current wisdom suggests you need a SUSPEND statement as the last line of the SP in order for the client to read the return values. I can't comment one way or the other - I never seemed to need it.

Here's an example:

CREATE PROCEDURE aProc(InValue1 integer, InValue2 varchar(2)
RETURNS (Result char(1), OutValue1 numeric(18,3), OutValue2 varchar(50)
as
BEGIN
Result = 'F';
....;

OutValue1 = {{some value}};
OutValue2 = {{some value}};
if (some condition) then
Result = 'T';
SUSPEND; /* Try it without this first */
END

You don't mention what you are using for your client application but, in Delphi, you will need to use ParamByName to get the return values of this row.

In IB you can use a stored procedure to predefine a dataset of multiple rows. The syntax is different. Check out the FOR SELECT...INTO...DO....SUSPEND syntax in the DataDef and LangRef manuals. This type of SP you invoke with SELECT, not EXECUTE, and you don't need to declare return values...although you may do so if you want to emulate Result for some reason...and you must do so if the SP is creating output columns other than those written INTO the column variables by the SELECT.

(Please don't cross-post !!)

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________