Retrieve data returned by a Stored Procedure: Php & MS SQL SERVER

Question

This question relates to:

PHP Version 5.3.6

Microsoft Drivers for PHP for SQL Server

I am trying to properly retrieve data from a stored procedure.

This question assumes the existence of the following stored procedure:

CREATE PROCEDURE test_procedure
AS
     BEGIN
           SET NOCOUNT ON
           --A bunch of SQL statements
           --More SQL statements
           SELECT 'Doctor Who Rules!'
     END

I've tried the following code which runs through all of my commands but does not return the data from the final SELECT.

$sql = "EXEC test_procedure;";
$result = sqlsrv_query($conn,$sql);
$next_result = sqlsrv_next_result($result); // returns a boolean
$row = sqlsrv_fetch_array($result);

Using sqlsrv_execute does not work with the above code either.

How can I return the data geneated by the stored procedure above via PHP?

Thank you.

Addendum #1 (Classic ASP Counterpart)

sqlCMD = "EXEC test_procedure;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open _ConnectionString_Variable_
Set rs = conn.Execute(sqlCMD)

I would get back a recordset with one row that has one field with the data "Doctor Who Rules!"

You'd need to specify an output parameter in the procedure - right now you've only got a single input (@foo). msdn.microsoft.com/en-us/library/ms187926.aspx (see 'out' and 'output'). — Marc B
– Marc B, Commented Jul 27, 2011 at 21:47
Thank you for the response, but I’m not sure your comment is relevant to my question. Let me clarify. The issue at hand is that I should be getting back the final SELECT in the stored procedure, but I am not. I have edited the question so that the SELECT statement in the stored procedure so that it is clearer and not dependent on the input. The question remains the same. — Ralph M. Rivera
– Ralph M. Rivera, Commented Jul 27, 2011 at 21:58
It may seem not relevant, but please provide the users table definition. I had similar issue recenty, but I won't be sure about the answer unless I see the table. — jacek
– jacek, Commented Jul 27, 2011 at 22:21
I think I may have been too granular with my example. I have removed elements that are not relevant to my question (e.g, I have removed the input variable @foo and changed the SQL statement to something that does not rquire a table). I have also added a snippet of classic ASP code which behaves in the manner I would like the PHP to behave. — Ralph M. Rivera
– Ralph M. Rivera, Commented Jul 27, 2011 at 23:33
Table definition, please. What types of columns do you have there? — jacek
– jacek, Commented Jul 28, 2011 at 7:56

lazpuruag · Accepted Answer · 2012-06-07 01:16:22Z

1

I just hit the same problem a few minutes ago, and this thread pointed me towards a working solution (I'm not sure that it is a "right"solution, but it works).

Actually, the problem was not being caused by "SET NOCOUNT ON". In my case, it was a couple of PRINT statements that were left there after debugging.

Looks like any output from the SP other than the actual recordset, causes the problem.

answered Jun 7, 2012 at 1:16

lazpuruag

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Ralph M. Rivera · Accepted Answer · 2011-07-28 12:22:57Z

I figured out the problem.

The PHP code in my example will work properly if SET NOCOUNT ON is ommited from the Stored Procedure. With SET NOCOUNT ON in the procedure, there is no data stream back to PHP and thus, the results of the last SELECT never makes it back.

Therfore, this Stored procedure...

CREATE PROCEDURE test_procedure
     @foo varchar(25)
AS
     BEGIN
          --A bunch of SQL statements
          --More SQL statements
          SELECT @foo
END

...will work perfectly with this PHP...

$sql = "EXEC test_procedure 'Dr. Who Rules!';";
$result = sqlsrv_query($conn,$sql);
$next_result = sqlsrv_next_result($result);
$row = sqlsrv_fetch_array($result);

You will end up with a recordset with the string "Doctor Who Rules!"

Collectives™ on Stack Overflow

Retrieve data returned by a Stored Procedure: Php & MS SQL SERVER

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related