Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hamster
Starting Member
2 Posts |
 Posted - 2002-05-17 : 00:33:45
|
| What is the different between Store Procedure and User Define Function ?I had create a Store ProcedureExample: -----------CREATE PROCEDURE sp_ViewAllUser_param @UserName varchar(50)ASSelect * from v_Userwhere UserName = @UserName GOExample for Function:---------------------CREATE FUNCTION UserDetail (@UserName varchar)RETURNS TableASReturn(Select * from v_User where UserName = @UserName)Can someone tell me why I should use Function instead of Store Procedure?Thank You ! |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-05-17 : 04:10:10
|
quote:
What is the different between Store Procedure and User Define Function ?
In your example. the distinct is blurred, and so this explains your question.The main diff as far as coding goes is that you can include the return from a function in your set-based code - eg select * from users where lastlogindate < getdate()You can't do that with a stored procedure.Personally, I'm of the view that Functions should return values without interacting with tables, eg wordbackwards('madam'), and I do everything that interracts with tables via an sp.ie - if I were you, in your example - I'd use a stored proc. I'd be surprised if there's much of a performance difference because SQL Server is pretty smart and will probably optimize....However, I'd be interested to hear what others think (if they get around to responding!!!) --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-17 : 09:42:21
|
| I agree with rrb . . . except, you can use the resultset of the function directly as a derived table in a join . . . sure beats pumping a proc's resultset into a temp table or table var so you can run dml against it. But I would only write this as a func if that is the type of stuff you are going to do with the resultset.EDIT: you should only start proc names with "sp_", if they are in the master database . . . read more here<O>Edited by - Page47 on 05/17/2002 09:43:45 |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-05-17 : 09:51:51
|
| I think you'll notice the difference when you won't be able to retreive a resultset from a function to your front end using ADO. I don't think that ADO sees functions as usable command objects. |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-17 : 10:50:05
|
| Don't forget, User functions only allow deterministic SQL functions (ones that ALWAYS return the same result for a fixed set of parameters).For instance, you should get an error if you attempt to use the GETDATE function within the user defined function. This can be a pain on select queries where you want 'the last 30 days' transactions as of now. |
|
|
|
|
|
|
Function Vs Store Procedure