How to fetch SQL Server procedure returned value?

Question

I'm trying to execute the procedure down below using Laravel query builder and the result always empty array,

SQL Procedure

CREATE FUNCTION [dbo].[FConvTxtUnt] 
(
    -- Add the parameters for the function here
    @itmid int ,
    @number numeric(12,2) 
)
RETURNS nvarchar(150)
AS
BEGIN
--     
    declare @txtunitqty as nvarchar(150)
    set  @txtunitqty=''

    declare @factors as numeric(12,2)
    declare @result as int
    declare @result2 as numeric(12,2)

    set @result=1
    declare tcur  cursor for 
    select itemunitid,unitname 
    from stock_ItemsUnits
    inner join  stock_Units on stock_Units.unitid=stock_ItemsUnits.unitid 
    where itmid = @itmid 
    order by itemunitid
                
   open tcur ;

   declare @Itemunitid int; 
   declare @unitname nvarchar(50);
   fetch next from tcur into @Itemunitid,@unitname
   while (@@fetch_status = 0)
   BEGIN
      set @factors = (
          select EXP(SUM(LOG(factor)))  
          from  stock_ItemsUnits
          where itmid = @itmid and Itemunitid>=@Itemunitid 
      )
  
      if ( @factors=1)
      begin
         set   @txtunitqty=@txtunitqty +'/'+cast (@number as nvarchar(50))+@unitname
      end
      else
      begin
         set @result= @number/@factors
         set @result2= @number%@factors  
         set   @txtunitqty=@txtunitqty +'/'+cast (@result as nvarchar(50))+@unitname
      end
      set @number=@result2

      fetch next from tcur into @Itemunitid,@unitname;
   end

   close tcur;
   deallocate tcur;

   RETURN @txtunitqty

END

Implementation code,

return DB::select("EXEC FConvTxtUnt ?,?", [$itemId ,$quantity] );

The result always seems like,

[]

Can anyone tell me what is wrong, considering that I don't have permission to modify the procedure itself, which I can't change the return to output.

Honestly, i would recommend rewriting that function though. Multi-line scalar functions can run slowly, and a WHILE in SQL runs very* slowly. I would strongly recommend rewriting your function to be an inline table value function, which uses a set based solution. — Thom A
– Thom A ♦, Commented Apr 26, 2022 at 9:02
That cursor is a horror show, it's going to be really slow. Looks like you can do this with a window function EXP(SUM(LOG(factor)) OVER (PARTITION BY itmid ORDER BY Itemunitid DESC ROWS UNBOUNDED PRECEDING)) — Charlieface
– Charlieface, Commented Apr 26, 2022 at 14:05

Zhorov · Accepted Answer · 2022-04-28 08:23:22Z

1

I will focus on the PHP part of the problem. You are trying to execute a function, not a procedure, so you simply need to change the statement:

DB::select("SELECT dbo.FConvTxtUnt(?, ?)", [$itemId, $quantity]);

I understand that you can't change the function, because you ... don't have permission to modify the procedure ..., but you need to consider very carefully the comments about using loops and cursors.

edited Apr 28, 2022 at 8:23

answered Apr 26, 2022 at 8:25

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fetch SQL Server procedure returned value?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related