user2129416 user2129416 - 2 months ago 20
PHP Question

Call stored procedure in PHP using sqlsrv driver

I'm trying to call procedure in PHP using SQLSRV driver of Microsoft. Here is my stored procedure, calling function. But it shows me an error:


"Error in executing statement. Array ( [0] => Array ( [0] => IMSSP
[SQLSTATE] => IMSSP [1] => -14 [code] => -14 [2] => An invalid
parameter was passed to sqlsrv_query. [message] => An invalid
parameter was passed to sqlsrv_query. ) )"


Stored procedure:

ALTER procedure [dbo].[getRelatedProductById]
(@productId int)
AS
BEGIN
declare
@id varchar(max),
@sql nvarchar(max)

BEGIN TRY
select @id = relatedProduct
from Product
where id = @productId

set @sql = 'select * from Product where id in(' + @id + ')' +'and id != '+ Convert(varchar, @productId)
exec sp_executesql @sql
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;

select @@error
print @@error
END CATCH
END

-- exec getRelatedProductById 1


PHP function:

public function getRelatedProduct($cid,$productId,$limit) {
$db=new db();
settype($productId, "integer");
$params = array(array($productId, SQLSRV_PARAM_IN));
$callSP = "{CALL getRelatedProductById(?)}";
$sql=sqlsrv_query($db, $callSP,$params);
if( $sql === false )
{
echo "Error in executing statement.\n";
die( print_r( sqlsrv_errors(), true));
}
}

kba kba
Answer

Well, sqlsrv_query expects as first argument resource from sqlsrv_connect function but you are passing instance of some mysterious class db. Maybe you should use variable $cid instead $db ($db and $limit seem unnecessary in your function).

$sql = sqlsrv_query($cid, $callSP, $params);