FireDAC and bi-directional stored procedure parameters in SQL Server.  I am diving into FireDAC, from a ADO starting point, and there are differences.

The SQL Server stored procedure below has two bidirectional parameters Error and Id. Description is input only, and the the procedure returns an int (0 = success, non-0 = check Error)

Error may be set, and Id is either a value > 0 which will be used as key, or if it is 0 or NULL, an auto-incremented value is returned through Id.

My ADO code which so far has translated easily for queries, etc – is barfing on invalid parameters for the stored proc.

How do you properly set up the bidirectional parameters?

How do you retrieve the output parameter values?

How do you retrieve the result value?

A simple code example would be appreciated, as the docs for TFDParams and Stored Procedures are Carefully Refined Awesome Prose.

{code}

CREATE PROCEDURE [dbo].[p_upsert_Description]

   @ErrorMsg varchar(1000) output,

   @Id int output,

   @Description varchar(100)

AS

BEGIN

    DECLARE @aId int;

    SET @ErrorMsg= ”;

    SET @aId= 0;

    BEGIN TRY

        SELECT @aId= Id FROM t_descriptions WHERE Id=@Id;

        IF @aId> 0

        BEGIN

            UPDATE t_descriptions

            SET Description=@Description

            WHERE Id =@aId;

            SELECT @Id=@aId;

        END

        ELSE

        BEGIN

            DECLARE @TmpIdTable table(ID int)

            INSERT INTO t_descriptions (

                CreatedTime,

                Description

            )  OUTPUT inserted.ID into @TmpIdTable

            VALUES (

                SYSDATETIME(),

               @Description

            );

            SELECT TOP 1 @Id= ID  FROM @TmpIdTable

        END

    END TRY

    BEGIN CATCH

        SELECT @ErrorMsg= ERROR_MESSAGE();

        RETURN SELECT ERROR_NUMBER();

    END CATCH

    RETURN 0;

END

{code}