Call a Sql function that returns a value

Show us your exact code and exact error message.

This is my SQL Server Function. It returns a decimal value.
ALTER FUNCTION [dbo].[fn_check_gl_voucher_diff] (@voucher_no int)

RETURNS decimal(15,2)

AS

BEGIN

-- Declare the return variable here

DECLARE @diff decimal(15,2);

select @diff = sum(dr_amt) - sum(cr_amt) from gl_voucher_d where voucher_no = @voucher_no;

RETURN @diff;
END
------------------

This is how I call the SQL Server function.
$sql = DB::prepareSQL("select dbo.fn_check_gl_voucher_diff(" .$keys["voucher_no"].")");

$rv=DB::Query($sql);
-------------------

And this is the error message.
php error happened
Technical information

Error type 4096

Error description Object of class QueryResult could not be converted to string

URL localhost/gl_voucher_h_edit.php?page=edit&submit=1&editid1=7&

Error file C:\Users\Jack\Documents\PHPRunnerProjects\iPMS II\output\include\gl_voucher_h_events.php

Error line 133

SQL query select dbo.fn_check_gl_voucher_diff(7)

Is this your complete PHP code? Looks like error is happening somewhere else.

This is not the complete code, but the DB::Query line is the line reported error. I cross-checked the output .php file.

This is not the complete code, but the DB::Query line is the line reported error. I cross-checked the output .php file.

I have modified my SQL Server function to a Table-valued function as below and modified my event code to fetch the record. Then it works. I am wondering DB::Query does not support SQL Server's Scalar-valued functions. I haven't tested it with the SQL Server's aggregate functions.
----------------------

SQL Server function
CREATE FUNCTION [dbo].[fn_check_gl_voucher_diff_1] (@voucher_no int)

RETURNS TABLE

AS

RETURN

select (sum(dr_amt) - sum(cr_amt)) as diff from gl_voucher_d where voucher_no = @voucher_no;
----------------------

PHPver function
CREATE FUNCTION [dbo].[fn_check_gl_voucher_diff_1] (@voucher_no int)

RETURNS TABLE

AS

RETURN

select (sum(dr_amt) - sum(cr_amt)) as diff from gl_voucher_d where voucher_no = @voucher_no;
----------------------

PHP Runner event code
$sql = DB::prepareSQL("select * from dbo.fn_check_gl_voucher_diff_1 (" .$keys["voucher_no"].")");

$rs=DB::Query($sql); <---------------------- This statement passed, no error

$data = $rs->fetchAssoc();

if ($data[diff] <> 0)

{

echo ("D/R and C/R Amount not equal.");

return false;

[Okay, saw you fixed the probem, i will just leave the suggestions here for future reference.]
The error line only shows a hint where the error is triggered, actual problem may be from other part that leads to the trigger.
I will try to give some suggestions:

1. Try running the store procedure manually in a 3rd party SQL editor or MySQL Command line
   The error line says with SQL query select dbo.fn_check_gl_voucher_diff(7) => the error is triggered likely within your store-procedure or other part of the codes.
   );
   Run manually "dbo.fn_check_gl_voucher_diff(7)" and see if there is error in the SQL Editor. If there is, fix it, before you try on PHPRunner.
   
2. Could also be the Stored procedure returns something else then what you expected. So print out the whole $data array and see what the stored procedure returns
   

Note: You paste a partial code with no end bracket } or Is it that you REALLY overlook the end } bracket ?
3. Consider to use PHPRunner Database API in the event instead and disabled the store procedure
It seems your stored procedure can be easily done by PHPRunner Database API, i.e. you may have unnecessarily complicate your solution with the stored procedures.
When there is execution error in your stored procedure, the error will likely be in the MySQL Server log and not captured by PHP. This makes troubleshooting tricky and difficult to simulate your problem

on our PC.
ACP

1. Try running the store procedure manually in a 3rd party SQL editor or MySQL Command line
   The error line says with SQL query select dbo.fn_check_gl_voucher_diff(7) => the error is triggered likely within your store-procedure or other part of the codes.
   );
   Run manually "dbo.fn_check_gl_voucher_diff(7)" and see if there is error in the SQL Editor. If there is, fix it, before you try on PHPRunner.
   
2. Could also be the Stored procedure returns something else then what you expected. So print out the whole $data array and see what the stored procedure returns
   

Note: You paste a partial code with no end bracket } or Is it that you REALLY overlook the end } bracket ?
3. Consider to use PHPRunner Database API in the event instead and disabled the store procedure
It seems your stored procedure can be easily done by PHPRunner Database API, i.e. you may have unnecessarily complicate your solution with the stored procedures.
When there is execution error in your stored procedure, the error will likely be in the MySQL Server log and not captured by PHP. This makes troubleshooting tricky and difficult to simulate your problem

on our PC.
ACP

I can tell the SQL function works fine and returns the value as expected. My second SQL Server function is just a workaround. It doesn't really solve my problem.
BY the way, what do you mean by DatabaseAPI? Isn't DB::Query a databaseAPI function?
If you look into my two SQL Server functions, they return different data type, the first one returns a single value and the second one returns a table, even my result is a single value.

I can see two possible issues here:

1. The following is wrong
   

Should be

2. Also, it expects a field named "diff" while you are returning the variable. Your SQL query should be something like this:

1. The following is wrong
   

Should be

2. Also, it expects a field named "diff" while you are returning the variable. Your SQL query should be something like this:

Thank you for your suggestion and it works. Despite the error "diff", the assignment of a field name wors perfectly. Thank you!!