?
Solved

How do I return two values by using TSQL Functions?

Posted on 2011-10-06
4
Medium Priority
?
227 Views
Last Modified: 2012-05-12
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
Comment
Question by:iscivanomar
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 800 total points
ID: 36927995

You cant return two values with a scaler function.

You can use a Table valued function or a procedure
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 800 total points
ID: 36928026
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 36928116
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
 

Author Closing Comment

by:iscivanomar
ID: 36934296
Thank you ewangoya. I use a Table-Valued Function
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question