[RESOLVED] Difference

[asm]

Hi,

Please any body tell us -
1. what is the difference between Procedure and function
2. what is the main difference between SQL server 7 & SQl server 2000.

these question asked in interview.


thanks

asm

[dannymking]

1. A Procedure is a set of code instructions that run without a return value... A function returns a resulting value to the calling procedure/function after its set of code has completed..

http://courses.mgmt.dal.ca/mba6519/notes/notes7.htm

2. This might help..

http://www.greenspun.com/bboard/q-an...?msg_id=00BVxp

[szlamany]

Both a STORED PROCEDURE and a USER DEFINED FUNCTION encapsulate SQL statements and have parameters that can be passed to within the SPROC or UDF.

In effect that makes them very similar.

But philosophically, they are very different. A UDF is supposed to work on the parameters passed and return out a value. They are not intended to do TABLE I/O - but of course this rule can be broken.

A UDF can be part of a SELECT statement.

SELECT STUID,DBO.GETLASTNAME(STUNAME) FROM STUDENTTABLE

The function DBO.GETLASTNAME will take the STUNAME column from STUDENTTABLE and extract the LAST NAME of the student, for example.

A STORED PROCEDURE cannot be part of a select statement

SELECT STUID,GETLASTNAME_P(STUNAME) <-- this does not work...

STORED PROCEDURES usually return a recordset or perform some larger database operation. We calculate payroll in stored procedures - we adjudicate medical claims in stored procedures - we schedule students into high school classes in stored procedures.

With UDF's we put dashes in social security numbers, build address lines.

[kaffenils]

A lot of information has already been given, but I would also like to mention one difference between SP and UDF that I find useful. A UDF that return a table data type can be used as a recordset in select statements (select * from dbo.fn_myfunction) and can therefore be a good alternative to views since you can do much more in a UDF than you can do in a view.

[szlamany]

A lot of information has already been given, but I would also like to mention one difference between SP and UDF that I find useful. A UDF that return a table data type can be used as a recordset in select statements (select * from dbo.fn_myfunction) and can therefore be a good alternative to views since you can do much more in a UDF than you can do in a view.

Could you show a sample please of how you start the UDF off, so that we can see how you indicate a return value of a TABLE?

[kaffenils]

Could you show a sample please of how you start the UDF off, so that we can see how you indicate a return value of a TABLE?

This is a function we use to calculate daily work load based on a job system we have by transforming hours assigned between a start and end date into hours per day. I have removed the actual T-SQL code since that would be meaningless to include.

CREATE function dbo.fn_CalculateResourceWorkLoad (@ContactId int, @StartEndDateByMonth bit=0) 
returns @calendar table([date] datetime primary key,
hours_assigned float default 0,
hours_total float default 0,
no_work_day bit default 0)
 
as 
begin 
 
-- Execute TSQL here to populate the @calendar table.
 
return 
end

We then use the function in a SELECT statement to calculate work load grouped by month, week, year, quarter etc.

set datefirst 1 
 
select year([date]) as year,datepart(ww,[date]) as week,
round(sum(hours_assigned),1) as hours_assigned,
sum(hours_total) as hours_total,
min([date]) as start_date,
max([date]) as end_date
from dbo.fn_CalculateResourceWorkLoad(@ContactId,0)
group by year([date]),datepart(ww,[date])

You could have done that same by only using stored procedures, but I thought that using the UDF as a recordset was so elegant that I just had to do it.

[szlamany]

Nice example - thanks.

Using a UDF to do table I/O in the "resultset" itself is a nice use - I like it.

I believe that the "don't do table I/O in a UDF" issue is more related to putting a UDF in a WHERE clause that does I/O, as the query optimizer would have a problem evaluating a query path.

We've got a SPROC that determines eligibility for a patient when entering a health claim. Whenever we are asked to populate a "whole" table of patients with eligibility, we have a huge problem - since a SPROC cannot be used as a SELECT column. I'm going to consider changing that SPROC to a UDF so that it can be used in more places.

Thanks again!