PHP using SQL Server CLR Stored Procedure how to consume return value

Question

I have stored procedures written in CLR (C#) that are sitting in SQL Server DB. When I run those procedures I get return value as well as output. Procedure code is:

using(SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlPipe pipe = SqlContext.Pipe;

                conn.Open();


                string table = tableName.ToString();
                string columns = columnList.ToString();
                string values = valueList.ToString();
                string types = typeList.ToString();

                cmd.Connection = conn;

                cmd.CommandText = @"
INSERT INTO " + table + " (" + columns + ") VALUES(" + values + ");SELECT SCOPE_IDENTITY();";
                pipe.ExecuteAndSend(cmd);
return 48;

Now when I run this code in SQL Management I get:

now in PHP app I want to access either SCOPE_IDENTITY() value sent to output or custom return value (both will work in my case).

$params = array(
                            array(&FW::$session->requestUniqueId, SQLSRV_PARAM_IN),
                            array(&$this->rootTable, SQLSRV_PARAM_IN),
                            array(&$columns, SQLSRV_PARAM_IN),
                            array(&$values, SQLSRV_PARAM_IN),
                            array(&$types, SQLSRV_PARAM_IN)
                        );

$sql="EXEC sp_data_insert @requestId=?, @tableName=?, @columnList=?, @valueList=?, @typeList=?";

$prep=sqlsrv_prepare($this->conn,$sql,$params);
if($prep!==false)
{
    $res=sqlsrv_execute($prep);
    if($res)
    {
            //HOW TO ACCESS RETURN VALUE OR OUTPUT VALUE YIELD BY SQL PROCEDURE HERE ?
    }
}

Can anyone tell me how to access return value 48 returned by CLR stored procedure or/and output value (SCOPE_IDENTITY)?

I know I can add output paramter but this would be a pain, since I would have hundrets of procedures to change. I need to access these values without changing SQL CLR.

Problem is that I cannot make this work in PHP. What can be wrong? This code does execute the stored procedure (I see data in DB changed), but I cannot access return value in PHP:

$sql="DECLARE @ret int EXEC @ret=$stored_procedure; SELECT @ret";
$prep=sqlsrv_prepare($this->conn,$sql,$params);
if($prep!==false)
{
    $res=sqlsrv_execute($prep);
    if($res)
    {
        while($row=sqlsrv_fetch_array($prep))
        {
            print('row='.print_r($row,true));
        }
    }
}

Answer 1

You don't need to change the SQLCLR stored procedure, you need to get rid of it entirely. It is absolutely pointless in this usage. Not only do you gain nothing, but you now have a system that is far more convoluted and harder (i.e. costlier) to maintain than the already convoluted nature of the yet-another-generic-query-mechanism.

You could do all of this in T-SQL with sp_executesql (though still not a great idea) and it would be clearer as to how to get the SCOPE_IDENTITY() value.

You already know how to get the return value as you are doing it in the SSMS screen shot. You just need to re-SELECT that variable after the EXEC so that it will be a result set $res.

You might could capture the SCOPE_IDENTITY() by creating a local temporary table (or table variable) with a single INT column and then doing INSERT INTO #tmp ([ID]) EXEC proc;, similar to:

GO
CREATE PROC #ttt
AS
SELECT 1
UNION ALL
SELECT 56788;

RETURN 55;
GO

DECLARE @T TABLE (Col1 INT);
DECLARE @Y INT;

INSERT INTO @T ([Col1])
 EXEC @Y = #ttt;

SELECT @Y, *
FROM @t;