October 31, 2007 at 4:49 am
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
October 31, 2007 at 5:00 am
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
November 1, 2007 at 1:40 am
Thanks Vaidiyanathan.
karthik
November 1, 2007 at 7:30 am
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.
October 23, 2008 at 4:06 am
can we use stored procedure in the User defined function
October 31, 2008 at 5:42 am
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.
October 31, 2008 at 7:59 am
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?
May 7, 2009 at 5:44 am
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
May 7, 2009 at 6:25 am
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.
February 3, 2010 at 6:08 am
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
January 27, 2011 at 10:02 pm
Date datatype we can't send as input parameter in function eventhough we can able to return a table.
January 27, 2011 at 10:40 pm
thangirala.anitha (1/27/2011)
Date datatype we can't send as input parameter in function eventhough we can able to return a table.
Interesting. Here is code where I have an input parameter defined using the Date type in SQL Server 2008, and the function works just fine.
create function dbo.DateTest (
@pDate date
)
returns varchar(24)
as
begin
return(datename(mm, @pDate));
end;
go
select dbo.DateTest('20110127');
go
drop function dbo.DateTest;
go
February 22, 2011 at 1:51 pm
thats gr8 information
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
April 20, 2011 at 11:02 pm
for better understanding you can check this link also
[url= http://dotnetpeoples.blogspot.com/2011/04/what-is-difference-between-stored.html%5D%5B/url%5D
April 20, 2011 at 11:41 pm
for better understanding you can visist this link also
http://dotnetpeoples.blogspot.com/2011/04/stored-procedure-vs-user-defined.html
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply