SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between Stored procedure and functions


Difference between Stored procedure and functions

Author
Message
karthik M
karthik M
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2975 Visits: 2584
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
karthik M
karthik M
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2975 Visits: 2584
Thanks Vaidiyanathan.

karthik
mrpolecat
mrpolecat
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 Visits: 856
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.






Madhuri-442856
Madhuri-442856
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 48
can we use stored procedure in the User defined function
Shekhu
Shekhu
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 130
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.
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12457 Visits: 18576
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?
welcomesiju
welcomesiju
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39954 Visits: 38564
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)
purushoth.Chennai
purushoth.Chennai
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 174
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
thangirala.anitha
thangirala.anitha
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
Date datatype we can't send as input parameter in function eventhough we can able to return a table.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search