• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

How do I return two values by using TSQL Functions?

I need to get the occupied positions and capacity of a DriveIN Rack in a warehouse.

I am sending the name or ID of the warehouse to the function, and with a query, I am getting the occupied positions and capacity.

My code:
USE [WMS_KRAFT750_DEV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER FUNCTION [WMS.Inventory].[GetOccupiedPosAndCapacity]
(
      @Location nvarchar(50),
      @OccupiedPos int =0 out,
      @Capacity int =0 out
)
RETURNS bigint
AS
BEGIN

select @OccupiedPos= COUNT(sup.Id), @Capacity= sl.Capacity
from [WMS.Storage].StockUnitPosition sup
    inner join [WMS.Storage].StoragePosition sp on sp.Id = sup.Id_StoragePosition
      inner join [WMS.Storage].StorageLocation sl on sl.Id = sp.Id_StorageLocation
      inner join WMS_Kraft.StockUnit ksu on ksu.ID = sup.Id_StockUnit
      inner join [WMS.Storage].StorageLocationType slt on slt.Id = sl.Id_LocationType
      inner join [WMS.Storage].StorageType ST ON ST.Id = sl.Id_StorageType
      left outer join WMS_Kraft.AutoHKLocation ahk on ahk.LocationNameFrom = sl.Name
      left outer join WMS_Kraft.DispositionCodes dc1 on (dc1.Code = ksu.DispositionCode1)
      left outer join WMS_Kraft.DispositionCodes dc2 on (dc2.Code = ksu.DispositionCode2)
      left outer join WMS_Kraft.DispositionCodes dc3 on (dc3.Code = ksu.DispositionCode3)
      where (isnull(dc1.HouseKeepingOnlyManual,0) <> 1 and isnull(dc2.HouseKeepingOnlyManual,0) <> 1
            and isnull(dc3.HouseKeepingOnlyManual,0) <> 1) and sl.E80ReworkAreaOverflow = 0
              and ahk.ID is null and ST.Name IN ('Warehouse') and sup.Booking = 0
              and sl.Name = @Location
      
      RETURN @OccupiedPos,@Capacity;
END

The problem is in the RETURN value.


Thank you.

0
iscivanomar
Asked:
iscivanomar
  • 2
3 Solutions
 
Ephraim WangoyaCommented:

You cant return two values with a scaler function.

You can use a Table valued function or a procedure
0
 
Ephraim WangoyaCommented:
Here are two ways you can achieve this

1. Stored Procedure

 
CREATE PROCEDURE GetOccupiedPosAndCapacity
(
      @Location nvarchar(50),
      @OccupiedPos int =0 out,
      @Capacity int =0 out
)
AS
BEGIN

select @OccupiedPos= COUNT(sup.Id), @Capacity= sl.Capacity
from [WMS.Storage].StockUnitPosition sup
    inner join [WMS.Storage].StoragePosition sp on sp.Id = sup.Id_StoragePosition 
      inner join [WMS.Storage].StorageLocation sl on sl.Id = sp.Id_StorageLocation 
      inner join WMS_Kraft.StockUnit ksu on ksu.ID = sup.Id_StockUnit
      inner join [WMS.Storage].StorageLocationType slt on slt.Id = sl.Id_LocationType 
      inner join [WMS.Storage].StorageType ST ON ST.Id = sl.Id_StorageType 
      left outer join WMS_Kraft.AutoHKLocation ahk on ahk.LocationNameFrom = sl.Name
      left outer join WMS_Kraft.DispositionCodes dc1 on (dc1.Code = ksu.DispositionCode1)
      left outer join WMS_Kraft.DispositionCodes dc2 on (dc2.Code = ksu.DispositionCode2)
      left outer join WMS_Kraft.DispositionCodes dc3 on (dc3.Code = ksu.DispositionCode3)
      where (isnull(dc1.HouseKeepingOnlyManual,0) <> 1 and isnull(dc2.HouseKeepingOnlyManual,0) <> 1 
            and isnull(dc3.HouseKeepingOnlyManual,0) <> 1) and sl.E80ReworkAreaOverflow = 0 
              and ahk.ID is null and ST.Name IN ('Warehouse') and sup.Booking = 0 
              and sl.Name = @Location
END

Open in new window


  exec GetOccupiedPosAndCapacity  @Location,  @OccupiedPos,  @Capacity

2. Table valued function

 
CREATE FUNCTION GetOccupiedPosAndCapacity
(
      @Location nvarchar(50)      
)
RETURNS 
	@PosAndCapacity TABLE 
	(
		OccupiedPos int, 
		Capacity int
	)
AS
BEGIN
    insert into @PosAndCapacity
	select COUNT(sup.Id), sl.Capacity
	from [WMS.Storage].StockUnitPosition sup
    inner join [WMS.Storage].StoragePosition sp on sp.Id = sup.Id_StoragePosition 
      inner join [WMS.Storage].StorageLocation sl on sl.Id = sp.Id_StorageLocation 
      inner join WMS_Kraft.StockUnit ksu on ksu.ID = sup.Id_StockUnit
      inner join [WMS.Storage].StorageLocationType slt on slt.Id = sl.Id_LocationType 
      inner join [WMS.Storage].StorageType ST ON ST.Id = sl.Id_StorageType 
      left outer join WMS_Kraft.AutoHKLocation ahk on ahk.LocationNameFrom = sl.Name
      left outer join WMS_Kraft.DispositionCodes dc1 on (dc1.Code = ksu.DispositionCode1)
      left outer join WMS_Kraft.DispositionCodes dc2 on (dc2.Code = ksu.DispositionCode2)
      left outer join WMS_Kraft.DispositionCodes dc3 on (dc3.Code = ksu.DispositionCode3)
      where (isnull(dc1.HouseKeepingOnlyManual,0) <> 1 and isnull(dc2.HouseKeepingOnlyManual,0) <> 1 
            and isnull(dc3.HouseKeepingOnlyManual,0) <> 1) and sl.E80ReworkAreaOverflow = 0 
              and ahk.ID is null and ST.Name IN ('Warehouse') and sup.Booking = 0 
              and sl.Name = @Location
	
	RETURN 
END
GO

Open in new window


  select @OccupiedPos = OccupiedPos ,  @Capacity = Capacity
  from dbo.GetOccupiedPosAndCapacity (@Location)

0
 
Anthony PerkinsCommented:
You could potentially return the values using a Scalar function by combining them in a single integer using bitwise logic and then splitting them when making the call.  But that is way overkill and you would be better off using a Table Valued Function.
0
 
iscivanomarAuthor Commented:
Thank you ewangoya. I use a Table-Valued Function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now