CODEWITHSUNDEEP
phpsqlsrv
user2129416
user2129416

Reputation: 15

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));
        }
}

Upvotes: 0

Views: 2436

Answers (1)

kba
kba

Reputation: 4310

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);

Upvotes: 1

Related Questions