FireDAC and bi-directional stored procedure parameters in SQL Server.

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}

Comments