Difference between Stored procedure and functions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Difference between Stored procedure and...

16 posts, Page 1 of 2

1 2 »»

Difference between Stored procedure and functions

Rate Topic

Display Mode

Topic Options

Author

Message

karthik M

Posted Wednesday, October 31, 2007 4:49 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 2,008, Visits: 2,472

Hi Example,

I have faced the question mentioned in the subject text box. I said to those peoples,' SP will not return any value and function should return value'.Am i correct ?

Your valuable explanations and examples are always welcome.

karthik

Post #416974

VSVaidya

Posted Wednesday, October 31, 2007 5:00 AM

SSCrazy

Group: Banned Members
Last Login: Today @ 7:52 AM
Points: 2,327, Visits: 304

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

Post #416979

karthik M

Posted Thursday, November 01, 2007 1:40 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 2,008, Visits: 2,472

Thanks Vaidiyanathan.

karthik

Post #417408

mrpolecat

Posted Thursday, November 01, 2007 7:30 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 8:10 AM
Points: 445, Visits: 840

karthikeyan (10/31/2007)

What you stated was the basic difference between a subroutine and a function in programming languages.

Post #417507

Madhuri-442856

Posted Thursday, October 23, 2008 4:06 AM

SSChasing Mays

Group: General Forum Members
Last Login: Sunday, January 23, 2011 11:43 PM
Points: 616, Visits: 48

can we use stored procedure in the User defined function

Post #590341

vivekshekhar-743222

Posted Friday, October 31, 2008 5:42 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, July 25, 2012 6:38 AM
Points: 66, Visits: 55

Good point .. this is another difference between procedure and a function

When you create a function ,SQL server will allow you to call a procedure from the Function.
However when you execute the function , it will error out with the messgae "Only functions and extended stored procedures can be executed from within a function".

It means only extended stored procedures can be called from a function.

Post #594884

Matt Miller (#4)

Posted Friday, October 31, 2008 7:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 7,002, Visits: 13,999

Vaiydeyanathan.V.S (10/31/2007)

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration

It's more accurate to say it as: "cannot be used to change ANYTHING". Functions in SQL are not allowed to have any kind of permanent effect, so they can't modify ANYTHING permanent. The only thing they're allowed to change would be a table variable created within the function itself.

5) Cannot be used with XML FOR clause

Not true. It's all in how you use it.

6) Cannot have transaction within function

If you can't modify anything - there's no point in allowing transactions.....

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP

You can't join to it DIRECTLY. You can use some 4easy techniques to join to it (OPENQUERY for one thing).

5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #594996

welcomesiju

Posted Thursday, May 07, 2009 5:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 07, 2009 5:27 AM
Points: 1, Visits: 1

Hi:

Although both functions and sp's are prcomiled sql statements there exists some differences between them.

1. Functions must return a value(scalar,inline table or multi statement table) whereas stored proc may or may not retun a value.
2.Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6.transaction related statement can be handled in sp whereas it can't be in function.
7. stored procedures can call a funtion or another sstored proc similarly a function can call another function and a stored proc.The catch with function is that no user defined stored proc can be called.Only extended/system defined procs can be called.

Hope this will be helpful and if there's any correction let me know.

Regards
Siju George

Post #711919

Lynn Pettis

Posted Thursday, May 07, 2009 6:25 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 21,832, Visits: 27,850

May I also suggest you take the time to read about these things in Books On-Line. It is FULL of useful information that may answer many of your questions. If, after reading BOL, you still have questions (which is entirely possible as BOL though full of information may not explain it well enough at times) we'd be happy to explain further.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #711957

purushoth.Chennai

Posted Wednesday, February 03, 2010 6:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 18, 2012 6:20 AM
Points: 143, Visits: 143

Hi Friends,

Can you please tell me whats the data type that cant be pass as a I/P param for funtion???

and

Whats the data type that cant be return from a Function???

Thanks in advance

Post #858565

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions