Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Difference between Stored procedure and functions Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2007 4:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
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
Posted Wednesday, October 31, 2007 5:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Tuesday, July 22, 2014 3:58 AM
Points: 2,622, Visits: 327
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
Posted Thursday, November 1, 2007 1:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:23 AM
Points: 2,025, Visits: 2,521
Thanks Vaidiyanathan.

karthik
Post #417408
Posted Thursday, November 1, 2007 7:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 8:10 AM
Points: 445, Visits: 840
karthikeyan (10/31/2007)
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.



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







Post #417507
Posted Thursday, October 23, 2008 4:06 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing 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
Posted Friday, October 31, 2008 5:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 6:26 AM
Points: 66, Visits: 67
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
Posted Friday, October 31, 2008 7:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:24 PM
Points: 7,139, Visits: 15,191
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
Posted Thursday, May 7, 2009 5:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 7, 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
Posted Thursday, May 7, 2009 6:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 23,299, Visits: 32,047
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.




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
Posted Wednesday, February 3, 2010 6:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:15 AM
Points: 175, Visits: 152
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 »

Add to briefcase 12»»

Permissions Expand / Collapse