title: Returning values from stored procedure using TSQL and PHP
body: <p>Is it possible to get data returned from a Stored procedure in PHP using SQLSRV connecting to a windows server DB. At the moment, the code I have commits the query succesfully but doesn't return anything. The loop where the rows output does not run even once (ie the object is empty)</p>
  
  <p>Here's the PHP</p>
  
  <pre><code>/* Set up and execute the first query. */
  $sql1 = "
  USE CRDCMS2PublicDevelopment
  EXEC SearchForXML6b 
  @SessionID = 973543,
  @LineID = 892245,
  @SortOrder = 'YearPublished DESC, SortTitle ASC',
  @PageNumber = 1,
  @RecordsPerPage = 20
  ";
  $params1 = array( $orderId, $qty, $productId );
  $stmt1 = sqlsrv_query( $conn, $sql1/*, $params1*/ );
  
  /* Display the value of the output parameters. */   
  while ( $obj = sqlsrv_fetch_object( $stmt1 ) ) {       
      //SET PARAMETERS - SET TERMS    
      echo 'loop';  
      echo $obj-&gt;listingXML;
  }
  
  /* If both queries were successful, commit the transaction. */
  /* Otherwise, rollback the transaction. */
  if( $stmt1 ) {
       sqlsrv_commit( $conn );
       echo "Transaction committed.&lt;br /&gt;";
  } else {
       sqlsrv_rollback( $conn );
       echo "Transaction rolled back.&lt;br /&gt;";
  }
  </code></pre>
  
  <p>Here's the Stored procedure</p>
  
  <pre><code>USE [ my db name ]
  GO
  /****** Object:  StoredProcedure [dbo].[ SPROC name ]    Script Date: 12/08/2013 14:45:37 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  ALTER PROCEDURE [dbo].[ SPROC name ]
  @DatabaseID int = 1,
  @PageNumber int = 1,
  @RecordsPerPage int = 20,
  @SessionID int = 0,
  @LineID int = 0,
  @SearchFor nvarchar(max) = '',
  @UserID int = 0,
  @SortOrder nvarchar(max) = 'YearPublished DESC, SortTitle ASC',
  @Published int = 3, -- -1=all, 0=not published, 1=prov, 2=full, 3=all published
  @SearchXML nvarchar(max) = '', -- xml representation of advanced search
  @ShowSelected int = 0,
  @IsWebSearch int = 0
  AS
  BEGIN
      SET NOCOUNT ON;
  
      --SET @recordsPerPage = 50
  
      -- ----------------------------------------------------------------------------------------------
      -- don't allow a null page number or recs per page
      -- ----------------------------------------------------------------------------------------------
      if @PageNumber is null
          SET @PageNumber = 1
      if @RecordsPerPage is null
          SET @RecordsPerPage = 20
  
      -- ----------------------------------------------------------------------------------------------
      -- Set up the header of the SQL to run
      -- ----------------------------------------------------------------------------------------------
      DECLARE @sql nvarchar(max)
      set @sql = '
          DECLARE @sql nvarchar(max)
          DECLARE @startRow int
          DECLARE @endRow int
          SET @startRow = '+STR(((@PageNumber-1) * @RecordsPerPage)) + '
          SET @endRow = '+STR(((@PageNumber) * @RecordsPerPage)+1) + '
  
          DECLARE @total int
  
          DECLARE @t TABLE (RowNumber int IDENTITY, AccessionNumber bigint not null, SortTitle nvarchar(450), YearPublished int, DatabaseID int, RecordTypeID int, Selected int NULL, Source nvarchar(450))'
  
  
  
          SET @sql = @sql + '
    DECLARE @hits varchar(max)
  
    SELECT @hits = Hits from UserHitTemp where LineID = ' + LTRIM(RTRIM(STR(@lineID))) + '  
  
          insert into @t ( AccessionNumber, SortTitle , YearPublished , DatabaseID , RecordTypeID , Source)
              SELECT C.AccessionNumber, SortTitle, YearPublished, DatabaseID, RecordTypeID, ISNULL((select top 1 FieldText from SearchField where AccessionNumber = C.accessionNumber and SearchTag = ''SO''),'''') as Source 
              FROM dbo.SplitAccessionList(@hits) H
              JOIN CRDDocument C on C.AccessionNumber = H.AccessionNumber ORDER BY ' + @SortOrder
  
  
  
      SET @sql = @sql + '
          set @total = @@ROWCOUNT
  
          DECLARE @xml XML
          SET @xml = (
          SELECT TOP 1 @total as "@RecordCount",
              0 as "@SelectedCount",
              '+LTRIM(RTRIM(STR(@SessionID)))+' as "@SearchSessionID",
              '+LTRIM(RTRIM(STR(@PageNumber)))+' as "@PageNumber",
              '+LTRIM(RTRIM(STR(@RecordsPerPage)))+' as "@RecordsPerPage",
              '+LTRIM(RTRIM(STR(@LineID)))+' as "@LineID",
              ''advanced)'' as "@SearchType",
  
              (   SELECT dbo.GetRecordForListingXML(AccessionNumber, RowNumber,ISNULL(Selected,0)) 
                  FROM @t T
                  WHERE T.RowNumber &gt; @startRow and T.RowNumber &lt; @endRow
  
                  FOR XML PATH(''''), ELEMENTS, TYPE
              )
              FOR XML PATH(''listing''), ELEMENTS
          )
  
          SELECT @XML as listingXML'
  
      -- ----------------------------------------------------------------------------------------------
      -- Run the SQL to extract the results as XML
      -- ----------------------------------------------------------------------------------------------
  
      PRINT @sql
  
      EXEC sp_executesql @sql
  
  END
  </code></pre>
  
  <p>Please note: if i copy and paste the exact sql used in the php ($sql1) it returns one row correctly in SQL server management studio. I think what I'm trying can be done, is this the right way? </p>
  
  <p>Thanks.</p>