October 4, 2005 at 7:05 am
Hi!
My problem is that at times I don't understand whether I should create function for a specific purpose or a stored procedure. I'll be glad if someone can give me an exact idea on where to use what and why.
Waiting for responses
Indu Jakhar
October 4, 2005 at 7:44 am
If you have a table function then you can do a SELECT on the output of that function where as with a stored procedure you can't, at least you can't do it without bouncing the results through to a separate table.
If you have a function that gives a row by row value then you probably should be doing this in the front end application rather than at the SQL level.
October 5, 2005 at 12:15 am
Functions return data "in line" - they can be used as though they were a table in a query, or can return a single scalar value (eg sin(angle) where sin() is your function). Functions cannot modify the data in your database - ie, they cannot have ANY side effects.
Stored procedures are good for encapsulating database logic - you give it some parameteters and it will execute a number of steps, inserts, updates, deletes, etc and can return one or more resultsets. The resultsets given as output from stored procs are best dealt with in client side code - you can only access the first resultset if calling the proc from other SQL code. Stored procs CAN (and usually do) affect your data.
Also, functions are not available in SQL Server v7.
October 5, 2005 at 11:40 am
Just to add to the above:
Function can return only one resultset at a time. Stored Proc can return more than one resultset at a time.
Also Stored Procs support output parameters while UDF's don`t.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply