CODEWITHSUNDEEP
phpsql-serverlaraveleloquentquery-builder
Esso
Esso

Reputation: 23

How to fetch SQL Server procedure returned value?

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.

Upvotes: 0

Views: 223

Answers (1)

Zhorov
Zhorov

Reputation: 29993

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.

Upvotes: 1

Related Questions