MSSQL+Symfony 4.4 Stored Procedure empty result in doctrine

Question

I have a stored procedure. When I'm executing it from SQL Server Management Studio everything works OK and I'm getting a result. But when I'm executing it from my doctrine I'm getting always a null result, it doesn't matter whether it was successful or not.

CREATE procedure [dbo].[approve_pre_register_demand]
    @hashLink varchar(max) AS
BEGIN
    DECLARE @bClientId uniqueidentifier; 
    -- check is token valid and not deactivated
    BEGIN
        --get uuid by link
        SELECT @bClientId = [dbo].[b_client_pre_registration_link].[b_client_uuid] 
        FROM [dbo].[b_client_pre_registration_link]
        WHERE [dbo].[b_client_pre_registration_link].[hash_link] LIKE @hashLink
    END
    BEGIN
        IF NOT EXISTS(SELECT [b_client_uuid] FROM [dbo].[b_client_pre_registration_link] WHERE [hash_link] LIKE @hashLink)
            BEGIN
                RETURN 'Token does not exists'
            END
    END
    BEGIN
        IF NOT EXISTS(SELECT [dbo].[sa_temp_b_client].[uuid] FROM [dbo].[sa_temp_b_client] WHERE [dbo].[sa_temp_b_client].[uuid] LIKE @bClientId)
            BEGIN
                RETURN 'BClient does not exists'
            END
    END
    BEGIN
        IF NOT EXISTS(SELECT [dbo].[at_b_client_files].[b_client_uuid] FROM [dbo].[at_b_client_files] WHERE [dbo].[at_b_client_files].[b_client_uuid] LIKE @bClientId)
            BEGIN
                RETURN 'BClient files does not exists'
            END
    END
    -- move preregistration to permanent tables
    BEGIN
        BEGIN
            BEGIN TRANSACTION
                BEGIN

                    IF (@@error <> 0)
                        -- Отменить транзакцию, если есть ошибки
                        ROLLBACK
                END
            COMMIT
            SELECT 'RESULT' = @hashLink
        END
    END
END

Answer 1

In your last statement, you are returning:

SELECT 'RESULT' = @hashLink

If you test this in SQL Server Management Studio or Azure Data Studio, you will see the above produce one row with one column named RESULT:

DECLARE  @hashLink varchar(max) = 'test'
 
SELECT 'RESULT' = @hashLink;

In the other cases you have a RETURN, which will produce an error:

CREATE PROCEDURE dbo.TEST 
(
    @hashLink varchar(max)
)
AS
BEGIN;

    RETURN 'text'

END;

GO

EXEC dbo.TEST @hashLink = 'x'

like the following:

Msg 245, Level 16, State 1, Procedure dbo.TEST, Line 8 [Batch Start Line 14] Conversion failed when converting the varchar value 'text' to data type int.

Completion time: 2020-12-01T08:17:03.7036860+02:00

So, just change the RETURN statements to SELECT with alias.

---

Also, if you want to use a RETURN to get a value, this is how the value is consumed:

CREATE OR ALTER PROCEDURE dbo.TEST 
(
    @hashLink varchar(max)
)
AS
BEGIN;

    RETURN  5

END;

GO

DECLARE @A INT;

EXEC @A = dbo.TEST @hashLink = 'x'

SELECT @A;