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}

2 thoughts on “FireDAC and bi-directional stored procedure parameters in SQL Server.


  1. NVM: Found the reason.  I was missing


    SProc.FetchOptions.Items := SProc.FetchOptions.Items – [fiMeta];


    which caused the implicit SProc.Prepare to mess with my hand crafted params.

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.