Converting Stored Procedure to Function

  • Is it easy to convert SP to Function ,vice versa Function to SP ? What are the limitations for that? Can someone kindly show me how could I convert this following SP to UDF?

    USE [Db]

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER PROCEDURE [dbo].[uspInvoiceLines]

    (

    @InLinesPerPage int,

    @orderno int

    )

    AS

    DECLARE @TotalRows int

    DECLARE @Remainder int

    DECLARE @NumPages int

    DECLARE @NextPageRows int

    set @TotalRows= 0

    select ROW_NUMBER() OVER(ORDER BY L.ICode) as InvoiceRow, L.CusNo as CusID, L.ICode as P_id, L.OrderNo as Inv_No, L.IName as P_Desc,

    convert(varchar(8), L.DDate,3) as Del_date, cast(L.OrderQty as decimal(9,2)) as Qty, Size as size,

    L.Price as Price, L.AmountCurrency as Amt

    into #tempInvoice

    from DataLine L where l.OrderNo=@orderno

    SET @TotalRows= @@ROWCOUNT

    IF @TotalRows=0

    BEGIN

    WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
    BEGIN
    SET @TotalRows= @TotalRows+1
    INSERT #tempInvoice
    (InvoiceRow,
    CusID,
    P_id,
    Inv_No,
    P_Desc,
    Del_date,
    Qty,
    size,
    Price,
    Amt
    )
    VALUES
    (@TotalRows
    ,''
    ,''
    ,0
    ,''
    ,NULL
    ,0
    ,''
    ,0
    ,0
    )
    END
    END
    ELSE
    BEGIN
    SET @Remainder = @TotalRows%@InLinesPerPage -- get remainder
    IF @Remainder !=0
    BEGIN
    -- Get the current page increase by 1 becasue we have a remainder.
    SET @NumPages = @TotalRows/@InLinesPerPage +1
    SET @NextPageRows = @NumPages * @InLinesPerPage
    WHILE @TotalRows < @NextPageRows -- Add Blank Rows
    BEGIN
    SET @TotalRows= @TotalRows+1
    INSERT #tempInvoice
    (InvoiceRow,
    CusID,
    P_id,
    Inv_No,
    P_Desc,
    Del_date,
    Qty,
    size,
    Price,
    Amt
    )
    VALUES
    (@TotalRows
    ,''
    ,''
    ,0
    ,NULL
    ,''
    ,0
    ,''
    ,0
    ,0
    )
    END
    END
    END
    SELECT * from #tempInvoice order by InvoiceRow asc
    return

    [/code]

  • Check out the section under Limitation and Restrictions. https://msdn.microsoft.com/en-us/library/ms191320.aspx 
    I have to ask what are you trying to get out of a UDF that you are not getting from the stored procedure?


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Start by getting rid of the WHILE loops.  As I mentioned in another of your threads, there's absolutely no need for them just to generate blank rows.  After that, look at the syntax for creating an inline TVF, and it should be easy for you to move your code across.

    John

  • Newbi - Friday, January 20, 2017 7:00 AM

    Is it easy to convert SP to Function ,vice versa Function to SP ? What are the limitations for that? Can someone kindly show me how could I convert this following SP to UDF?USE [Db]GOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOALTER PROCEDURE [dbo].[uspInvoiceLines](@InLinesPerPage int,@orderno int)ASDECLARE @TotalRows intDECLARE @Remainder intDECLARE @NumPages intDECLARE @NextPageRows intset @TotalRows= 0select ROW_NUMBER() OVER(ORDER BY L.ICode) as InvoiceRow, L.CusNo as CusID, L.ICode as P_id, L.OrderNo as Inv_No, L.IName as P_Desc,convert(varchar(8), L.DDate,3) as Del_date, cast(L.OrderQty as decimal(9,2)) as Qty, Size as size,L.Price as Price, L.AmountCurrency as Amtinto #tempInvoicefrom DataLine L where l.OrderNo=@orderno SET @TotalRows= @@ROWCOUNTIF @TotalRows=0 BEGIN WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice. BEGIN SET @TotalRows= @TotalRows+1 INSERT #tempInvoice (InvoiceRow, CusID, P_id, Inv_No, P_Desc, Del_date, Qty, size, Price, Amt ) VALUES (@TotalRows ,'' ,'' ,0 ,'' ,NULL ,0 ,'' ,0 ,0 ) END ENDELSE BEGIN SET @Remainder = @TotalRows%@InLinesPerPage -- get remainder IF @Remainder !=0 BEGIN -- Get the current page increase by 1 becasue we have a remainder. SET @NumPages = @TotalRows/@InLinesPerPage +1 SET @NextPageRows = @NumPages * @InLinesPerPage WHILE @TotalRows < @NextPageRows -- Add Blank Rows BEGIN SET @TotalRows= @TotalRows+1 INSERT #tempInvoice (InvoiceRow, CusID, P_id, Inv_No, P_Desc, Del_date, Qty, size, Price, Amt ) VALUES (@TotalRows ,'' ,'' ,0 ,NULL ,'' ,0 ,'' ,0 ,0 ) END END ENDSELECT * from #tempInvoice order by InvoiceRow ascreturn


    CREATE FUNCTION [dbo].[fnInvoiceLines]
    (
    @InLinesPerPage     int,
    @orderno     int
    ) RETURNS @tempInvoice table ( InvoiceRow int
                                 , CusID int
                                 , P_id int
                                 , Inv_No int
                                 , P_Desc varchar(100)
                                 , Del_date char(8)
                                 , Qty  decimal(9,2)
                                 , size int
                                 , Price money
                                 , Amt money
                                 )
    AS
    BEGIN
       
       DECLARE @TotalRows int = 0
             , @Remainder int
             , @NumPages int
             , @NextPageRows int ;
       INSERT @tempInvoice
       SELECT
            Row_Number() OVER(ORDER BY L.ICode) as InvoiceRow
          , L.CusNo as CusID
          , L.ICode as P_id
          , L.OrderNo as Inv_No
          , L.IName as P_Desc
          , Convert(varchar(8), L.DDate,3) as Del_date
          , cast(L.OrderQty as decimal(9,2)) as Qty
          , Size as size
          , L.Price as Price
          , L.AmountCurrency as Price
       FROM DataLine L
       WHERE l.OrderNo=@orderno;
      
       SET @TotalRows= @@ROWCOUNT;
       IF @TotalRows=0
       BEGIN
          WHILE @TotalRows < @InLinesPerPage -- Add Blank Rows will generate blank invoice.
             BEGIN
                   SET @TotalRows= @TotalRows+1;
                   INSERT @tempInvoice
                      ( InvoiceRow
                      , CusID
                      , P_id
                      , Inv_No
                      , P_Desc
                      , Del_date
                      , Qty
                      , size
                      , Price
                      , Amt
                      )
                   VALUES
                      ( @TotalRows
                      , ''
                      , ''
                      , 0
                      , ''
                      , NULL
                      , 0
                      , ''
                      , 0
                      , 0
                      );
             END
       END
       ELSE
       BEGIN
          SET @Remainder =  @TotalRows%@InLinesPerPage -- get remainder
          IF @Remainder !=0
          BEGIN
             -- Get the current page increase by 1 becasue we have a remainder.
             SELECT @NumPages = @TotalRows/@InLinesPerPage  +1
                  , @NextPageRows = @NumPages * @InLinesPerPage;
             WHILE @TotalRows < @NextPageRows -- Add Blank Rows
             BEGIN
               
                SET @TotalRows= @TotalRows+1;
                INSERT  @tempInvoice
                   ( InvoiceRow
                   , CusID
                   , P_id
                   , Inv_No
                   , P_Desc
                   , Del_date
                   , Qty
                   , size
                   , Price
                   , Amt
                   )
                VALUES
                   ( @TotalRows
                   , ''
                   , ''
                   , 0
                   , NULL
                   , ''
                   , 0
                   , ''
                   , 0
                   , 0
                   );
             END
          END
       END
      
       RETURN
    END

  • You need to be aware of the implications. Changing a stored procedure into a function might not work because functions are meant to return something (scalar value or table) while the stored procedures are meant to fulfill a process.
    If you're able to convert the stored procedure into a function, you might end with a multi-statement table-valued function which are very slow. The option there is to only convert them when you are able to convert them into an inline table valued function which are very fast when used correctly.
    Here's an untested example that replaces your loop with a tally table.

    CREATE FUNCTION [dbo].[InvoiceLines]
    (
      @InLinesPerPage  int,
      @orderno  int
    )
    RETURNS TABLE AS
    RETURN
    WITH
    E(n) AS(
      SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
    ),
    E2(n) AS(
      SELECT a.n FROM E a, E b
    ),
    E4(n) AS(
      SELECT a.n FROM E2 a, E2 b
    ),
    cteTally(n) AS(
      SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
      FROM E4
    )  
    select ROW_NUMBER() OVER(ORDER BY L.ICode) as InvoiceRow,
      L.CusNo as CusID,
      L.ICode as P_id,
      L.OrderNo as Inv_No,
      L.IName as P_Desc,
      convert(varchar(8), L.DDate,3) as Del_date,
      cast(L.OrderQty as decimal(9,2)) as Qty,
      Size as size,
      L.Price as Price,
      L.AmountCurrency as Amt
    from DataLine L
    where l.OrderNo=@orderno
    UNION ALL
    SELECT n + Row_Count
       ,''
       ,''
       ,0
       ,''
       ,NULL
       ,0
       ,''
       ,0
       ,0
    FROM cteTally t
    JOIN (SELECT COUNT(*) Row_Count
        FROM DataLine l
        where l.OrderNo = @orderno) c ON t.n <= @InLinesPerPage - (c.Row_Count % @InLinesPerPage)
                  OR (c.Row_Count = 0 AND t.n <= @InLinesPerPage);

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I'm currently thinking of converting a TVF to a stored procedure.
    There are complications, especially in terms of usability, but i think it worth to try.

    Functions execute code in a single thread, no parallel processing.
    On big data sets even perfectly written iTVF's would be slower than procedures, except the cases when you need to generate sequential sets (like Tally).

    _____________
    Code for TallyGenerator

  • Sergiy - Wednesday, January 25, 2017 3:19 PM

    I'm currently thinking of converting a TVF to a stored procedure.
    There are complications, especially in terms of usability, but i think it worth to try.

    Functions execute code in a single thread, no parallel processing.
    On big data sets even perfectly written iTVF's would be slower than procedures, except the cases when you need to generate sequential sets (like Tally).

    Scalar UDFs and multi-statement functions can only execute code in a single thread but inline table valued functions can get a parallel plan. 

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply