January 20, 2017 at 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]
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]
January 20, 2017 at 7:10 am
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?
January 20, 2017 at 7:14 am
John
January 23, 2017 at 12:33 pm
Newbi - Friday, January 20, 2017 7:00 AMIs 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;January 23, 2017 at 2:14 pm
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);
January 25, 2017 at 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).
_____________
Code for TallyGenerator
January 25, 2017 at 9:27 pm
Sergiy - Wednesday, January 25, 2017 3:19 PMI'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.
-- 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