sqlsrv - user defined table problem

Question

Hello there,

I am new in using sqlsrv, but it was almost successful to update my codes from the mssql PHP extension.

Now I have only one big issue: there is a user defined type (dbo.pt_int_pair), which has 2 columns.

I use the following query:

DECLARE @honnan dbo.pt_int_pair
INSERT INTO @honnan (p1,p2) VALUES(2012,0)

SELECT * FROM @honnan

The DECLARE - INSERT INTO part runs OK, sqlsrv_rows_affected() returns 1 for this, and the corresponding number if inserting more lines. But there is no answer for the SELECT part of the query, the resource is not empty, it has got the link, but no answer inside. sqlsrv_has_rows() returns false, but sqlsrv_errors() is empty as well (as sqlsrv_query() returned the resource and not false...)

Everything else runs OK, I can use variables, subqueries, procedures etc., only this user defined thing goes wrong (it was OK as we used the PHP mssql extension...).

Any ideas? Thank You in advance!

Answer 1

Are you running the query as a multi-statement batch?

If so, don't forget to call sqlsrv_next_result(), to step through each result in the batch.  E.g.:

$conn=sqlsrv_connect('server', $options);

$tsql = 'DECLARE @honnan dbo.pt_int_pair
INSERT INTO @honnan (p1,p2) VALUES(2012,0)
SELECT * FROM @honnan';

if ( ($stmt = sqlsrv_query($conn, $tsql)) )
{
	$inserted_rows = sqlsrv_rows_affected($stmt);
	
	if ( sqlsrv_next_result($stmt) )
	{
		$row = sqlsrv_fetch_array($stmt);
		print_r($row);
	}
}

---

Rob