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}
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
Post a Comment