User Defined Function vs Stored Procedure
-
I am designing a script that will clean folders on the server harddrive.
Pass in a string containing folder location and all files are deleted.
Is there any advantage for using a UDF or a regular Stored Proc to house this code?
Thanks
-
Well let's see. If you write it as a function then I would think you would want to write it such that only one folder at a time is deleted...
You could use something like the following to clean all your folders in 1 sql statement
SELECT dbo.f_CleanDirectory(sFolder)
FROM FoldersToDelete...
VS just calling a procedure that only allows one folder to be deleted at a time...
EXEC dbo.usp_CleanUpFolders @sFolder
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
I don't know whether this matters to you at all or not, but to me, using a UDF for this seems counter-intuitive.
UDFs should take an input value, do something to it, and return a modified output variable that should be related to the input variable.
I assume the only thing this "function" might output is whether or not it was successful, or possibly how many files were deleted. That kind of functionality is something I would intuitively expect from a sproc, but not a UDF.
If you just wanted to count the files in a folder, a UDF is a more intuitive approach:
NumberOfFiles = CountFiles(FolderName)
Just my FormatCurrency(0.02)...
-
UDF's (user defined functions) are functions at their purest form.
You pass in data, and you get ~something back. The UDF doesn't "do" anything , except return a value to you.
In tsql, you ~can read from tables in the database in an udf. You cannot update or insert records in an UDF. The only time you read from the db, is when the value you return, is based on something from the database.
You do not want to (or can't really) use a UDF to do the actually deleting of the folders. You might use it to return a table of all the folders, and your arguement to the procedure is the rootFolder name.
UDF's are very useful, when you remember that they are functions which ~return something, not ~do something (inside the udf).
Here is an example UDF. Notice I don't ~do anything.. I only return a value to the caller.
--"inline" udf example
Use pubs
if exists (select * from sysobjects where id = object_id('fnc_is_invoice_and_qty_greater_than_10') and xtype = 'FN')
drop function fnc_is_invoice_and_qty_greater_than_10
GO
CREATE FUNCTION fnc_is_invoice_and_qty_greater_than_10 (@payterms varchar(64) , @qty int)
--this is just a demonstration udf which takes 2 parameters and decides whether or not
--the @payterms has "invoice" in its text,and whether or not the qty is greater than 10
--this is a made up business rule, that makes no sense whatsoever
returns int
as
begin
declare @returnValue int
if CHARINDEX ( 'invoice' , @payterms ) > 0 AND @qty > 10
BEGIN
select @returnValue = 1
END
ELSE
BEGIN
select @returnValue = 0
END
RETURN(@returnValue)
end
GO
SET NOCOUNT ON
/*
SELECT *
FROM stores INNER JOIN
sales ON stores.stor_id = sales.stor_id
where qty > 10 and CHARINDEX ( 'invoice' , payterms) > 0
*/
--Let's declare a table, notice the last column, this is the column
--we'll later put out "business rule" return value in
declare @workingtable table
(
stor_id varchar(64) ,
stor_name varchar(64) ,
stor_address varchar(64) ,
city varchar(64) ,
state varchar(64) ,
zip varchar(64) ,
ord_num varchar(64) ,
ord_date varchar(64) ,
qty varchar(64) ,
payterms varchar(64) ,
title_id varchar(64) ,
--
isInvoiceAndQtyGreaterThan10 bit NULL
)
INSERT INTO @workingtable
(
stores.stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip ,
ord_num ,
ord_date ,
qty ,
payterms ,
title_id ,
isInvoiceAndQtyGreaterThan10
 
SELECT
stores.stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip ,
ord_num ,
ord_date ,
qty ,
payterms ,
title_id ,
--NOTICE that this column gets populated with the RESULT of our business rule
ark.fnc_is_invoice_and_qty_greater_than_10 (payterms , qty )
FROM
stores INNER JOIN
sales ON stores.stor_id = sales.stor_id
--Now let's look at the results, it looks like 3 rows
--meet our "business rule"
select isInvoiceAndQtyGreaterThan10, * from @workingtable
order by isInvoiceAndQtyGreaterThan10 desc
--This is a simple demo on how you can run calculations
--or business rules "in line" instead of looping over the entries
--with a cursor and doing checks at a cursor level
--The "in line" part means that
--one of the columns in your return set can be the value of a function
--and you can implement any business rule in the function
--The biggest things to determine are "what data" do i need to make my decision.
--This example says
--I need "qty" "payterms" to make my decision
--These become parameters to the udf
--then this example creates a "holder" column (isInvoiceAndQtyGreaterThan10)
--and will be filled with the results of the udf
SET NOCOUNT OFF
-
I'm not going to argue the political aspects of the above statements. However the following does work...
CREATE FUNCTION f_DeleteFile(@vFileName nvarchar(528))
RETURNS int
AS
BEGIN
DECLARE @cmd nvarchar(555)
, @Ret int
SET @cmd = 'del ' + @vFileName + ' /Q'
EXEC @Ret = master..xp_cmdshell @cmd
RETURN @Ret
END
SELECT dbo.f_DeleteFile('D:\Junk\test.txt')
I'm not sure I would want to do something like this but it sure does work.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply