Difference between function and stored procedure

Question

Post reply

Difference between function and stored procedure

Shakeel A. Ansari

SSC Veteran

Points: 267
More actions
November 22, 2005 at 10:22 pm

#95744

I would like to know the basic difference between function and stored procedure.
Thanks & regards
shakeel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

From what I know, the only real differences are:

1.Functions return a single value whereas stored procedures can return multiple.

2.Non-deterministic functions are not allowed in functions but are for stored procedures.

------------------------------
The Users are always right - when I'm not wrong!

Vladan SSC-Insane Points: 22072 More actions · Answer 2

In fact, table valued functions return table, so that means that function can return several values, too. The main difference from user's point of view is that you can use functions in the SELECT clause, and that certain things are not allowed in functions - see BOL for more (one of the most common examples is GETDATE() function).

For example, if you have a function dbo.CalculatePrice that calculates the sales price based on some customer and product settings, you can write:

SELECT o.cust_id, o.prod_id, p.prod_name, dbo.CalculatePrice(o.cust_id, o.prod_id)

FROM Orders o

JOIN Products p ON p.prod_id = o.prod_id

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Table-valued functions may also be used in the from clause

SELECT blah FROM dbo.SomeFunction(@Param1, @Param2)

In addition to the restrictions already listed, functions cannot have any side effect. Hence you cannot insert data into a table inside a function or create any object.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mrugajit Gaekwad SSC Journeyman Points: 85 More actions · Answer 4

Mrugajit Gaekwad

SSC Journeyman

Points: 85

November 24, 2005 at 6:57 pm

#605776

Stored Procedure (SP)

Bill Scott SSCrazy Points: 2338 More actions · Answer 5

Functions specifically return a scalar or table. the nice thing is a table value function can be used in your from clause, you just need to alias the result

select a.a, b.b

from sometable a inner join (myfunction()) b

on a.key = b.key

virat_108 SSC Enthusiast Points: 116 More actions · Answer 6

http://dotnetpeoples.blogspot.com/2011/04/stored-procedure-vs-user-defined.html

dbuendiab SSC Eights! Points: 993 More actions · Answer 7

In a few words, the difference between sp & functions is that functions yields an expression and so it can be used in a expression context (in SELECT clause, b.e.), whereas stored procedures are commands and cannot be invoked inside expressions.

With the table returning functions the function shifts into the FROM clause, and you could discuss if it still represents an expression. But still stored procedures are commands, and if you get a resultset from them you cannot use it later; the SELECT is returned to the client (Manager Studio, Query Assistant, etc.) and cannot be used further inside the SQL code (well, you can using INSERT table EXEC sp, but it's a little messy because you must create the table before getting the data, guessing what data types your columns will have).

Another point of view: stored procedures are scripts: you don't store them as files in your file system but use the same SQL Server as repository giving them a name, similarly as you do with the queries you want to keep saving them with a proper name in the server as Views.