Using Functions or Stored Procedures

Transact-SQL
1

I am relatively new to SQL. But I do come from a world of calling stored procedures. Here is what I would like to do and was wondering if it was possible. I have a lot of reports that need to calculate units sold over a time period by location. One sales report in particular needs to show Units Sold 7 Days, Units Sold 30 Days, Units Sold 60 Days along with other fields about each item sold. Instead of putting code in to calculate all of these date ranges individually I was wondering if there was a way to use a function or stored procedure to call off to with variables of days back from today, location, and item that would return the units sold value based on these inputs.

2

Either could do the trick, it would help if you would post the query you are currently using, though. Scalar functions can be poor performers. Have to be careful about scalability

3

Here is my report code and below that is the function I wrote I created views for sold30, sold60, sold120 that are in the select statement now. I want to be able to create a function or procedure or maybe one view using the procedure that I can call all the time that would replace making all of those views. Where the function would have the days as a parameter.
SELECT SkuCode, Description, Manufacturer, GM_LOCATION_ID, QUANTITY_ON_HAND, MIN_REORDER_POINT, MAX_REORDER_POINT, AVERAGE_COST, TRUE_COST,
UNITS_7DAYS, UNITS_30DAYS, UNITS_60DAYS, UNITS_120DAYS, UNITS_365DAYS, CMP_Price, MFGSkuCode, SkuType, FET, SuggQty, QTY_ONHAND_ALL,
BaseValue20, UNITS_60DAYSNT, Committed, OnOrder, Class, Consumer, Commercial
FROM Order_Dashboard
WHERE (GM_LOCATION_ID IN (@Store)) AND (Manufacturer IN (@MFG)) AND (Class IN (@Class)) AND (@SuppressSkus = 'Y') AND (SuggQty <= 0) OR
(GM_LOCATION_ID IN (@Store)) AND (Manufacturer IN (@MFG)) AND (Class IN (@Class)) AND (@SuppressSkus = 'N') AND (SuggQty > 0) OR
(GM_LOCATION_ID IN (@Store)) AND (Manufacturer IN (@MFG)) AND (Class IN (@Class)) AND (@SuppressSkus = 'A')

FUNCTION

USE [TD_REPORTS]
GO
/****** Object: UserDefinedFunction [dbo].[GetUnitsSoldLocation] Script Date: 10/13/2015 4:43:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[GetUnitsSoldLocation]
(
-- Add the parameters for the function here
@daysback as float,
@location as varchar(50),
@skunum as varchar(50) )
RETURNS TABLE

AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT SUM(Units) AS UnitsSold
FROM dbo.Sales_Detail
WHERE (DATEDIFF(dd, [Inv-Date], GETDATE()) <= @daysback) AND (DIM302 = 'WHOLESALE' OR
DIM302 = 'COMMERCIAL' OR
DIM302 = 'RETAIL') and DIM300 = @location and DIM100 = @skunum

4

First let me say the OR's will get you into trouble. You need parenthesis around the groupings.
In 2008R2 you cannot use a variable for an IN, however this may have changed.

If there is a way to calculate @daysback then the sum could be calculated in the main procedure.

5

The function looks basically fine. Try to rewrite your main query to use it and let's see where that gets us

6

That is the part I am unable to understand fully. How do I use the function in the main SQL code?

7

put the call to the function in the main query as cross apply

select ... 
cross apply myfun(margs)
1 Like
8

I'd rewrite the function to (1) avoid using any function against the table column and to (2) compute all the totals at once. This avoids the overhead of multiple calls. You can also create a separate function that does compute only a single total based on days passed, in case you need to do a custom total, i.e., not 7,30,60,120 or 365 days.

USE [TD_REPORTS]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetUnitsSoldLocation]
(	
    @max_daysback as int,
    @location as varchar(50),
    @skunum as varchar(50) 
)
RETURNS TABLE
AS
RETURN 
(
SELECT 
    SUM(CASE WHEN [Inv-Date] >= DATEADD(DAY, -  7, today_midnight) THEN Units ELSE 0 END) AS UNITS_7DAYS, 
    SUM(CASE WHEN [Inv-Date] >= DATEADD(DAY, - 30, today_midnight) THEN Units ELSE 0 END) AS UNITS_30DAYS, 
    SUM(CASE WHEN [Inv-Date] >= DATEADD(DAY, - 60, today_midnight) THEN Units ELSE 0 END) AS UNITS_60DAYS, 
    SUM(CASE WHEN [Inv-Date] >= DATEADD(DAY, -120, today_midnight) THEN Units ELSE 0 END) AS UNITS_120DAYS, 
    SUM(CASE WHEN [Inv-Date] >= DATEADD(DAY, -365, today_midnight) THEN Units ELSE 0 END) AS UNITS_365DAYS
FROM dbo.Sales_Detail
CROSS APPLY (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS today_midnight
) AS computed_control_values
WHERE [Inv-Date] >= DATEADD(DAY, -@max_daysback, today_midnight) AND
      DIM302 IN ('COMMERCIAL', 'RETAIL', 'WHOLESALE') AND
      DIM300 = @location AND
      DIM100 = @skunum
)
GO --end of function
1 Like
9

Then you main query becomes something like below. I'm not sure of what values to pass to the function, particularly what location value to pass, so definitely adjust that as needed:

SELECT 
    od.SkuCode, od.Description, od.Manufacturer, od.GM_LOCATION_ID, od.QUANTITY_ON_HAND, 
    od.MIN_REORDER_POINT, od.MAX_REORDER_POINT, od.AVERAGE_COST, od.TRUE_COST, 
    gusl.UNITS_7DAYS, gusl.UNITS_30DAYS, gusl.UNITS_60DAYS, gusl.UNITS_120DAYS, gusl.UNITS_365DAYS, 
    od.CMP_Price, od.MFGSkuCode, od.SkuType, od.FET, od.SuggQty, od.QTY_ONHAND_ALL, 
    od.BaseValue20, od.UNITS_60DAYSNT, od.Committed, od.OnOrder, od.Class, od.Consumer, od.Commercial
FROM Order_Dashboard od
CROSS APPLY dbo.GetUnitsSoldLocation ( 365, od.[?location?], od.MFGSkuCode ) AS gusl
WHERE 
    (od.GM_LOCATION_ID IN (@Store)) AND 
    (od.Manufacturer IN (@MFG)) AND 
    (od.Class IN (@Class)) AND 
    ( ((@SuppressSkus = 'Y') AND (od.SuggQty <= 0)) OR
      ((@SuppressSkus = 'N') AND (od.SuggQty > 0)) OR
      ((@SuppressSkus = 'A'))
    )
1 Like