Save Stored Procedure result in variable

Question

I've been looking for ways on how to get the result of the stored procedure I'm working on.

So when this SP is called on, it will return a series of numbers in an auto increment number. ex. 0000002743

I am not the one who created the SP and I'm a beginner in SQL Server but when I try to 'modify'it to at least see how it's made of, I have this:

ALTER PROCEDURE [dbo].[New_BatchCode] AS
DECLARE @x NUMERIC
UPDATE SysVars SET @x = BatchCode = BatchCode + 1
SELECT BatchCode = REPLACE(STR(@x,10),' ','0')

In SQL Server, I just get to run EXEC [dbo].[New_BatchCode]and the result will be displayed. But in PHP, I can't make it work. I tried the following:

1

$conn = $this->delphi_connect();
    $batch_code_query = "EXEC [dbo].[New_BatchCode];";
    $stmt3 = sqlsrv_query($conn,$batch_code_query); 
    if( $stmt3 === false )  
    {  
         echo "Error in executing statement 3.\n";  
         die( print_r( sqlsrv_errors(), true));  
    }
    if ($stmt3)  {
        while ($row = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)) {
            $ret[] = $row;
        }
    }
    sqlsrv_free_stmt( $stmt3);  
    sqlsrv_close( $conn); 
    print_r($ret);
    die();

2

$conn = $this->delphi_connect();
    $batch_code_query = "{call [dbo].[New_BatchCode]}";
    $stmt3 = sqlsrv_query($conn,$batch_code_query); 
    if( $stmt3 === false )  
    {  
         echo "Error in executing statement 3.\n";  
         die( print_r( sqlsrv_errors(), true));  
    }
    if ($stmt3)  {
        while ($row = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)) {
            $ret[] = $row;
        }
    }
    sqlsrv_free_stmt( $stmt3);  
    sqlsrv_close( $conn); 
    print_r($ret);
    die();

My SQL Server

My goal is to somehow store the result of the executed SP and insert it on another query. Like $batch_code = [result].

I'm not really sure what to do anymore. I'll be grateful for your help. Thanks!

Answer 1

You can solve this by creating a TRIGGER on your table for the updates, then you can use a #Temporal table or create a table (as needed) where you can store the inserted values from the INSERTED table and do whatever you need to do with them .