SQL Server stored procedure with output parameter

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
alt.ua
Posts: 15
Joined: Tue 08 Oct 2019 09:24

SQL Server stored procedure with output parameter

Post by alt.ua » Fri 30 Jul 2021 09:54

Hello

Embarcadero® Delphi 10.2 Version 25.0.31059.3231
UniDAC 8.4.4
Microsoft SQL Server 2019 Developer Edition (64-bit) version 15.0.2080.9 (X64) on Windows 10 Pro

1) Create SQL Server stored procedure with output parameter

Code: Select all

CREATE OR ALTER PROCEDURE Test
(
    @DataID UNIQUEIDENTIFIER
  , @Result NVARCHAR(MAX) OUTPUT 
)
AS
BEGIN
   SET NOCOUNT ON;
   SET @Result = LOWER( @DataID ) + N' / test';
   RETURN 33
END
GO


DECLARE @TestID UNIQUEIDENTIFIER = 'E6E06644-A098-49E5-9B17-30CFF83C1537';
DECLARE @Rc INT
DECLARE @Res NVARCHAR(MAX)

EXECUTE @Rc = Test @TestID, @Res OUTPUT
SELECT @Rc, @Res
2) Execute script ^^^, RETURN_VALUE and OUTPUT values has been returned

3) Create Delphi code

Code: Select all

var
  rc: Integer;
  Result: string;
begin
  Conn.Open;

  rc := Conn.ExecProc('test', ['E6E06644-A098-49E5-9B17-30CFF83C1537', Result]);

  Conn.Close;
end;
4) Execute Delphi code, RETURN_VALUE has been returned, OUTPUT value has NOT been returned

What's wrong ?

alt.ua
Posts: 15
Joined: Tue 08 Oct 2019 09:24

Re: SQL Server stored procedure with output parameter

Post by alt.ua » Tue 17 Aug 2021 07:55

Can I get a value of SQL Server stored procedure output parameter using TUniConnection.ExecProc ?

CristianP
Posts: 79
Joined: Fri 07 Dec 2012 07:44
Location: Timișoara, Romania

Re: SQL Server stored procedure with output parameter

Post by CristianP » Tue 17 Aug 2021 08:01

Have you tried with Conn.ParamByName('Result').AsString?

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SQL Server stored procedure with output parameter

Post by Stellar » Tue 17 Aug 2021 14:55

Hi,

ExecProc method as a first parameter accepts procedure name and massive constant as a second.
As a result, after procedure was executed the value for Result variable remains the same.
You may get the parameter value after executing the stored procedure:

Code: Select all

rc := Conn.ExecProc('test', ['E6E06644-A098-49E5-9B17-30CFF83C1537', Result]); 
Result := Conn.ParamByName('Result').AsString; 
Regards,
Sergey

alt.ua
Posts: 15
Joined: Tue 08 Oct 2019 09:24

Re: SQL Server stored procedure with output parameter

Post by alt.ua » Wed 18 Aug 2021 09:19

Code: Select all

Result := Conn.ParamByName('Result').AsString;
returns an empty value

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SQL Server stored procedure with output parameter

Post by Stellar » Wed 01 Sep 2021 05:48

HI,

Thanks for your request.
We have fixed the issue, and the fix will be included in the next UniDAC build.
To get the build, please send us a request via the e-support form. (devart.com the "Support"\"Request Support" menu).

Best regards,
Sergey,
Devart Support Team
www.devart.com

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SQL Server stored procedure with output parameter

Post by Stellar » Wed 31 Aug 2022 17:40

HI,

We are glad to inform you that the bug has been fixed and is included in the current build of the product.
You can download it on our website.

Best regards,
Sergey

Post Reply