Convert a function to a stored procedure to return all calculations from a table

I have a function (included) that I call from a stored procedure using CROSS APPLY.  I would really like it to be a stored procedure that would build a result set that I could join on if possible.

If anyone has any ideas, I'd appreciate it.

Here is the function

CREATE FUNCTION FNC_GETREPORTINFO(@ITEMID NUMERIC(18,0), @STOREID INT, @ASOFDATE DATETIME, @CALCMETHOD VARCHAR(1), @USINGCORP BIT)
RETURNS @REPORTTABLE  TABLE (MONTHLYDEPR NUMERIC(18,2)
                                        , YTDDEPR NUMERIC(18,2)
                                          , CURPERIODWO NUMERIC(18,2)
                                          , YTDWO NUMERIC(18,2)
                                          , TOTWO NUMERIC(18,2)
                                          , RBV NUMERIC(18,2)
                                          , ACCUM NUMERIC(18,2))
AS
BEGIN
DECLARE @MONTHLYDEPR NUMERIC(8,2)
DECLARE @YTDDEPR NUMERIC(8,2)
DECLARE @CURPERIODWO NUMERIC(8,2)
DECLARE @YTDWO NUMERIC(8,2)
DECLARE @TOTWO NUMERIC(8,2)
DECLARE @RBV NUMERIC(8,2)
DECLARE @ACCUM NUMERIC(8,2)
DECLARE @FIRSTDATE AS DATE
DECLARE @EOYDATE AS DATE
DECLARE @CURYEAR AS INTEGER
DECLARE @FYEM AS INTEGER
DECLARE @BOYDATE AS DATE

IF @USINGCORP <> 0
      SELECT @FYEM = FYEMONTH
            , @FIRSTDATE = FIRSTDOB
      FROM CORPSETUP
ELSE
      SELECT @FYEM = FYEMONTH
            , @FIRSTDATE = FIRSTDOB
      FROM SETUP

SET @EOYDATE = DATEADD(DD, -1, DATEADD(MM, 1, CONVERT(DATE, (CONVERT(NVARCHAR(2), @FYEM) + '/01/' + CONVERT(NVARCHAR(4), DATEPART(YY, @ASOFDATE)))  )))
SET @BOYDATE = DATEADD(YY, -1, DATEADD(DD, 1, @EOYDATE))


SELECT @MONTHLYDEPR = SUM(COALESCE(DT2.DEPRAMOUNT, 0))
      FROM DEPRECIATION D
      JOIN DEPRTRANSACTIONS DT2 ON
            D.ITEMID = DT2.ITEMID
            AND D.LOCATIONID = DT2.STOREID
      WHERE DT2.ITEMID = @ITEMID
            AND DT2.STOREID = @STOREID
            AND DT2.DEPRDATE = @ASOFDATE
IF @@ROWCOUNT = 0
      SET @MONTHLYDEPR = 0
IF @MONTHLYDEPR IS NULL
      SET @MONTHLYDEPR = 0

SELECT @YTDDEPR = SUM(DT2.DEPRAMOUNT)
      FROM DEPRECIATION D
      JOIN DEPRTRANSACTIONS DT2 ON
            D.ITEMID = DT2.ITEMID
            AND D.LOCATIONID = DT2.STOREID
      WHERE D.ITEMID = @ITEMID
            AND DT2.STOREID = @STOREID
            AND DT2.DEPRDATE BETWEEN @BOYDATE AND @ASOFDATE
IF @@ROWCOUNT = 0
      SET @YTDDEPR = 0
IF @YTDDEPR IS NULL
      SET @YTDDEPR = 0

SELECT @CURPERIODWO = DT2.WOAMT
      FROM DEPRECIATION D
      JOIN DEPRTRANSACTIONS DT2 ON
            D.ITEMID = DT2.ITEMID
            AND D.LOCATIONID = DT2.STOREID
      WHERE DT2.ITEMID = D.ITEMID
            AND DT2.STOREID = @STOREID
            AND DT2.DEPRDATE = @ASOFDATE
IF @@ROWCOUNT = 0
      SET @CURPERIODWO = 0
IF @CURPERIODWO IS NULL
      SET @CURPERIODWO = 0

SELECT @YTDWO = SUM(DT2.WOAMT)
      FROM DEPRECIATION D
      JOIN DEPRTRANSACTIONS DT2 ON
            D.ITEMID = DT2.ITEMID
      WHERE DT2.ITEMID = @ITEMID
            AND DT2.STOREID = @STOREID
            AND DT2.DEPRDATE BETWEEN @BOYDATE AND @ASOFDATE
IF @@ROWCOUNT = 0
      SET @YTDWO = 0
IF @YTDWO IS NULL
      SET @YTDWO = 0

SELECT @TOTWO = SUM(DT2.WOAMT)
      FROM DEPRECIATION D
      JOIN DEPRTRANSACTIONS DT2 ON
            D.ITEMID = DT2.ITEMID
            AND D.LOCATIONID = DT2.STOREID
      WHERE DT2.ITEMID = @ITEMID
            AND DT2.STOREID = @STOREID
            AND DT2.DEPRDATE <= @ASOFDATE
IF @@ROWCOUNT = 0
      SET @TOTWO = 0
IF @TOTWO IS NULL
      SET @TOTWO = 0

SELECT TOP 1 @RBV = DT2.RBV
      FROM DEPRECIATION D
      JOIN DEPRTRANSACTIONS DT2 ON
                  D.ITEMID = DT2.ITEMID
                  AND D.LOCATIONID = DT2.STOREID
      JOIN INVENTORY I ON
            I.ITEMID = D.ITEMID
            AND I.STOREID = D.LOCATIONID
      WHERE DT2.ITEMID = @ITEMID
            AND DT2.STOREID = @STOREID
            AND DT2.DEPRDATE <= @ASOFDATE
ORDER BY DT2.DEPRDATE DESC
IF @@ROWCOUNT = 0
      SET @RBV = 0
IF @RBV IS NULL
      SET @RBV = 0

SELECT TOP 1 @ACCUM = DT2.ACCUM
      FROM DEPRECIATION D
      JOIN DEPRTRANSACTIONS DT2 ON
                  D.ITEMID = DT2.ITEMID
                  AND D.LOCATIONID = DT2.STOREID
      JOIN INVENTORY I ON
            I.ITEMID = D.ITEMID
            AND I.STOREID = D.LOCATIONID
      WHERE DT2.ITEMID = @ITEMID
            AND DT2.STOREID = @STOREID
            AND DT2.DEPRDATE <= @ASOFDATE
      ORDER BY DT2.DEPRDATE DESC
IF @@ROWCOUNT = 0
      SET @ACCUM = 0
IF @ACCUM IS NULL
      SET @ACCUM = 0

INSERT INTO @REPORTTABLE VALUES (@MONTHLYDEPR, @YTDDEPR, @CURPERIODWO, @YTDWO, @TOTWO, @RBV, @ACCUM)

RETURN
END

Here is the procedure which calls it.


ALTER PROCEDURE PRC_BUILDREPORTDATA
      @ASOFDATE DATETIME
      , @USINGCORP BIT
      , @CALCMETHOD VARCHAR(1)
    , @EMNUMBER            INT             OUTPUT
    , @EMSEVERITY      INT             OUTPUT
    , @EMSTATE            INT             OUTPUT
    , @EMPROCEDURE      NVARCHAR(128)   OUTPUT
    , @EMLINE            INT             OUTPUT
    , @EMMESSAGE      NVARCHAR(4000)  OUTPUT
    , @MROWSUPDATED      INT                        OUTPUT
AS
DECLARE @ENUMBER            INT              
DECLARE @ESEVERITY        INT              
DECLARE @ESTATE            INT              
DECLARE @EPROCEDURE        NVARCHAR(128)    
DECLARE @ELINE            INT              
DECLARE @EMESSAGE            NVARCHAR(4000)  
DECLARE @ROWSUPDATED        INT                

DECLARE @FIRSTDATE AS DATE
DECLARE @EOYDATE AS DATE
DECLARE @CURYEAR AS INTEGER
DECLARE @FYEM AS INTEGER
DECLARE @BOYDATE AS DATE

BEGIN TRY

TRUNCATE TABLE DEPRTEMP
IF @USINGCORP <> 0
      SELECT @FYEM = FYEMONTH
            , @FIRSTDATE = FIRSTDOB
      FROM CORPSETUP
ELSE
      SELECT @FYEM = FYEMONTH
            , @FIRSTDATE = FIRSTDOB
      FROM SETUP

SET @EOYDATE = DATEADD(DD, -1, DATEADD(MM, 1, CONVERT(DATE, (CONVERT(NVARCHAR(2), @FYEM) + '/01/' + CONVERT(NVARCHAR(4), DATEPART(YY, @FIRSTDATE)))  )))
SET @BOYDATE = DATEADD(YY, -1, DATEADD(DD, 1, @EOYDATE))

INSERT INTO [dbo].[deprtemp]
           ([itemid]
           ,[storeid]
           ,[modelid]
           ,[serial_number]
           ,[statusid]
           ,[statusdesc]
           ,[method]
           ,[disposed]
           ,[Cost]
           ,[CostBasis]
           ,[Purchased]
           ,[StartDate]
           ,[PMo]
           ,[PYr]
           ,[MonthlyDepr]
           ,[YTDDepr]
           ,[CurPeriodWO]
           ,[YtdWO]
           ,[TOTWO]
           ,[RBV]
           ,[Accumdepr]
           ,[WOMo]
           ,[WOYr])
SELECT D.ITEMID
      , D.LOCATIONID
      , I.MODELID
      , I.SERIAL_NUMBER
      , I.STATUSID
      , S.DESCRIPTION
      , @CALCMETHOD
      , CASE @CALCMETHOD
            WHEN 'B' THEN D.WRITEOFFDATEBOOK
            ELSE D.WRITEOFFDATETAX
        END
      , I.COST
      , CASE @CALCMETHOD
            WHEN 'B' THEN D.COSTBASISBOOK
            ELSE D.COSTBASISTAX
        END
      , I.PURCHASED
      , D.STARTDATE
      , DATEPART(MM, @ASOFDATE)
      , DATEPART(YY, @ASOFDATE)
      , DTS.MONTHLYDEPR
      , DTS.YTDDEPR
      , DTS.CURPERIODWO
      , DTS.YTDWO
      , DTS.TOTWO
      , DTS.RBV
      , DTS.ACCUM
      , CASE @CALCMETHOD
            WHEN 'B' THEN
                  CASE WHEN D.WRITEOFFDATEBOOK IS NULL THEN 0
                        ELSE DATEPART(MM, D.WRITEOFFDATEBOOK)
                  END
            WHEN 'T' THEN
                  CASE WHEN D.WRITEOFFDATETAX IS NULL THEN 0
                        ELSE DATEPART(MM, D.WRITEOFFDATETAX)
                  END
        END
      , CASE @CALCMETHOD
            WHEN 'B' THEN
                  CASE WHEN D.WRITEOFFDATEBOOK IS NULL THEN 0
                        ELSE DATEPART(YY, D.WRITEOFFDATEBOOK)
                  END
            WHEN 'T' THEN
                  CASE WHEN D.WRITEOFFDATETAX IS NULL THEN 0
                        ELSE DATEPART(YY, D.WRITEOFFDATETAX)
                  END
        END
FROM DEPRECIATION D
JOIN INVENTORY I ON
      I.ITEMID = D.ITEMID
      AND I.STOREID = D.LOCATIONID
JOIN STATUS S ON
      S.STATUSID = I.STATUSID
CROSS APPLY FNC_GETREPORTINFO(D.ITEMID, D.LOCATIONID, @ASOFDATE, @CALCMETHOD, @USINGCORP) DTS
WHERE D.STARTDATE IS NOT NULL

END TRY
   BEGIN CATCH
        ROLLBACK  TRANSACTION PRC_MAKEPAYMENT
        SELECT @eMNumber = ERROR_NUMBER()
             , @eMSeverity = ERROR_SEVERITY()
             , @eMState = ERROR_STATE()
             , @eMProcedure = ERROR_PROCEDURE()
             , @eMLine = ERROR_LINE()
             , @eMMessage = ERROR_MESSAGE();

        IF OBJECT_ID(N'tempdb.. #TEMP_INCOME') IS NOT NULL
            DROP TABLE #TEMP_INCOME;

       RETURN 1
     END CATCH
    select  @eMNumber = @eNumber
      ,@eMSeverity = @eSeverity
      ,@eMState = @eState
      ,@eMProcedure = @eProcedure
      ,@eMLine = @eLine
      ,@eMMessage = @eMessage
    SET @MROWSUPDATED = 1


Thanks

Larry
lburnsinmagnoliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
For performance (especially) and for ease of use, you'd be much off keeping a table-valued function but making it an in-line tvf rather than a multi-line tvf.  Based on my (quick) review of the function code, that code can be converted into an itfv.
0
lburnsinmagnoliaAuthor Commented:
When you say multi-line tvf, are you saying you think my function is a multi-line one?  It is not, it returns a single line for the passed in ITEMID.
0
Scott PletcherSenior DBACommented:
The function itself is considered a multi-line function because it consists of more than just a single SELECT statement.  When you look at CREATE FUNCTION in BOL, you'll see that there are two types of table-valued functions, in-line and multi-line.  The in-line will perform orders of magnitude better since it is basically "compiled into" the running code, whereas the multi-line function must be called separately for every input row.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lburnsinmagnoliaAuthor Commented:
Ok,

I will try that and see if I get a boost in speed.

Will let you know.

Thanks
0
lburnsinmagnoliaAuthor Commented:
Thanks for the help.  Speed went from over an hour and 30 minutes down to under 15 minutes.
0
Scott PletcherSenior DBACommented:
Great!  Tuning the indexes also might even get it lower than that :-).
0
lburnsinmagnoliaAuthor Commented:
I have done that too!  Once I noticed the horrible speed, I did that hoping that was the main culprit.

Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.