CODEWITHSUNDEEP
phpsql-server-2008
etm124
etm124

Reputation: 2140

mssql_execute returns no results - better way to debug?

My connection is established, and I can call my stored procedure:

$pid = $_GET['pid'];
$conn = mssql_connect($server, $username, $password);
...
mssql_select_db('mydb',$conn);

$sproc = mssql_init('consultGetPatientData');
mssql_bind($sproc, '@pid', $pid, SQLINT2);

$result = mssql_execute($sproc);
echo mssql_num_rows($result);
if($result){    
    $row = mssql_fetch_assoc($result);      
...

My $pid value is being passed correctly. The $result value being is var_dump:

resource(3) of type (mssql result)

And msql_num_rows($result) yields 0 rows.

The user connecting to the database has sufficient access to the consultGetPatientData stored procedure:

Properties

When I manually run the stored procedure, desired results are being returned. My

My questions:

Is there a better way to debug these types of errors?

Is there anything glaring I am missing?

Thank you.

EDIT: stored procedure code as requested:

ALTER PROCEDURE [dbo].[consultGetPatientData] 
    @pid int = 0    
AS
BEGIN   
    SET NOCOUNT ON;

select 
    pd.pid, 
    pd.external_id,
    pd.fname, 
    pd.lname, 
    pd.DOB, 
    pd.ss, 
    pd.sex,
    pd.allergies,
    pb.date,
    pb.release
from 
    patient_data pd 
        inner join patient_booking_data pb on pd.pid = pb.pid
where 
    pd.pid = @pid
    and pb.date in (
        select MAX(date) from patient_booking_data where pid = @pid
    )
END

Upvotes: 0

Views: 2793

Answers (1)

Hackerman
Hackerman

Reputation: 12295

Change this:

$sproc = mssql_init('consultGetPatientData');
mssql_bind($sproc, '@pid', $pid, SQLINT2);

$result = mssql_execute($sproc);
echo mssql_num_rows($result);

To this:

$result=mssql_query("consultGetPatientData ".$pid.""); 
echo mssql_num_rows($result);

while($arr = mssql_fetch_assoc($result))
{
 echo $arr['sex']."</br>"; 
 //you can put the rest of the code to display the rest of the fields
}

Upvotes: 3

Related Questions