August 20, 2018 at 11:58 am
I need to process data in a function and return a result, but using a table variable is only good for small amounts of data, I can't use a temp table because it is a function. I tried to use a stored procedure that returns a value, but then I can't call it from a select. Any suggestions on a solution?
BTW: I need this in a function of store procedure because I call it from many places.
Here is what I am working with:
....
SELECT dbo.GetStatusCounter(StatusCode, @GatewayIDLocal, @OperationDateLocal) As TotalCount, StatusName, StatusCode, SortOrder
FROM Statuses
WHERE (Gateway = @GatewayIDLocal)
...
FUNCTION [dbo].[GetStatusCounter](@StatusCode NVARCHAR(100), @GatewayID Nvarchar(10), @TodaysDate DateTime )
RETURNS Int
AS
BEGIN
DECLARE @RESULT int
SET @RESULT = 0
DECLARE @HBStatuses table (AbillNo nvarchar(50))
Insert into @HBStatuses (AbillNo)
Select distinct AbillNo from HB_Statuses where HB_Statuses.StatusId = @StatusCode
Select @RESULT = count(distinct HB.AbillNo) FROM HB WITH (NOLOCK)
Inner Join @HBStatuses hs on hs.AbillNo = HB.AbillNo
Where st = @StatusCode
RETURN ISNULL(@RESULT, 0)
END
August 20, 2018 at 1:13 pm
thedrparker - Monday, August 20, 2018 11:58 AMI need to process data in a function and return a result, but using a table variable is only good for small amounts of data, I can't use a temp table because it is a function. I tried to use a stored procedure that returns a value, but then I can't call it from a select. Any suggestions on a solution?BTW: I need this in a function of store procedure because I call it from many places.
Here is what I am working with:
....SELECT dbo.GetStatusCounter(StatusCode, @GatewayIDLocal, @OperationDateLocal) As TotalCount, StatusName, StatusCode, SortOrder
FROM Statuses
WHERE (Gateway = @GatewayIDLocal)
...FUNCTION [dbo].[GetStatusCounter](@StatusCode NVARCHAR(100), @GatewayID Nvarchar(10), @TodaysDate DateTime )
RETURNS Int
AS
BEGINDECLARE @RESULT int
SET @RESULT = 0DECLARE @HBStatuses table (AbillNo nvarchar(50))
Insert into @HBStatuses (AbillNo)
Select distinct AbillNo from HB_Statuses where HB_Statuses.StatusId = @StatusCodeSelect @RESULT = count(distinct HB.AbillNo) FROM HB WITH (NOLOCK)
Inner Join @HBStatuses hs on hs.AbillNo = HB.AbillNo
Where st = @StatusCodeRETURN ISNULL(@RESULT, 0)
END
You can do this without a temp table or a table variable. Without knowing the structure of the underlying tables and their indexes, this is a best guess.CREATE FUNCTION [dbo].[GetStatusCounter](
@StatusCode NVARCHAR(100)
--, @GatewayID Nvarchar(10) -- Not sure why you have this parameter, as it is not used.
--, @TodaysDate DateTime -- Not sure why you have this parameter, as it is not used.
)
RETURNS Int
AS
BEGIN
RETURN (SELECT COUNT(DISTINCT HB.AbillNo)
FROM HB WITH (NOLOCK)
INNER JOIN HB_Statuses AS s
ON HB.AbillNo = s.AbillNo
AND HB.st = s.StatusId
WHERE s.StatusId = @StatusCode
);
END;
August 20, 2018 at 1:28 pm
You really don't need a table variable. Try the following (sorry, but you didn't provide any DDL or sample data so I couldn't test the code before posting):
CREATE FUNCTION [dbo].[fn_GetStatusCounter](@StatusCode NVARCHAR(100), @GatewayID Nvarchar(10), @TodaysDate DateTime)
RETURNS TABLE
AS RETURN (
SELECT
COUNT(DISTINCT [HB].[AbillNo]) AS [AbillNoCnt]
FROM
[HB]
WHERE
EXISTS(SELECT [hs].[AbillNo]
FROM [HB_Statuses] AS [hs]
WHERE
[hs].[HB_Statuses].[StatusId] = @StatusCode
AND [HB].[AbillNo] = [hs].[AbillNo])
AND [HB].[st] = @StatusCode
);
GO
SELECT
ISNULL([AbillNoCnt]) AS [TotalCount]
, [StatusName]
, [StatusCode]
, [SortOrder]
FROM
[Statuses]
OUTER APPLY [dbo].[fn_GetStatusCounter]([StatusCode], @GatewayIDLocal, @OperationDateLocal)
WHERE
([Gateway] = @GatewayIDLocal)
GO
August 21, 2018 at 11:14 am
Since you mentioned performance specifically, this might perform better:
Select Count(Distinct HB.AbillNo) AS AbillNo
FROM HB WITH (NOLOCK)
Where HB.st = @StatusCode And
Exists(
Select 1
From HBStatuses HBS
Where HBS.AbillNo = HB.AbillNo AND HBS.StatusId = @StatusCode
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply