How to capture MSSQL SP output / multiple select statement results?

[ankitmathur]

Hi All,

I'm facing a problem I'm unable to find a solution for. I hope fellow members would be able to help me out.


System Info:

PHP v5
MSSQL 2008
Linux box


Ok lets say I have a stored procedure called proc_getData which has 2 select
statements....

Create Proc proc_getdata
AS
Select COUNT(*) From Usr_Accounts;
select top 1 * from Usr_Accounts;

then within my PHP script I called the procedure.. EXEC proc_getData;

How would it be possible to set each select statement from the procedure
into different results? so I could loop through each as and when needed on
my page?


Or,


Second way I tried out is that I have a SP with a single select statement but with an
OUTPUT parameter.

How do I capture the OUTPUT parameter while work with the result set of
select statement also.

Create Proc proc_getdata1 @cnt int OUTPUT
AS
SELECT @cnt = COUNT(*) FROM Usr_Accounts; PRINT @cnt
Select top 1 * From Usr_Accounts;

SQL Statement being executed:

DECLARE @cnt INT SET @cnt = 0
Exec proc_getdata1 @cnt OUTPUT
SELECT @cnt

I want to capture my count variable @cnt to do paging while displaying the
Select statement result set on the web page.


Thanks and expecting a good response to my question
Ankit

[Atli]

How would it be possible to set each select statement from the procedure
into different results? so I could loop through each as and when needed on
my page?

You could try the mssql_next_resu lt function. It should allow you to use multiple result sets. (See the example in the manual)

PHP v5
MSSQL 2008
Linux box

You have a MSSQL server on a Linux box? Or is it a remote server?

[ankitmathur]

Hi Atli,

Your response sure has helped me inch closer. I'm still trying to use this function of PHP as I didn't had much info about it. Will get back if I get stuck again.

THANKS
Ankit