Difference between Stored procedure and functions

  • 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

  • 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

  • Thanks Vaidiyanathan.

    karthik

  • 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.


  • can we use stored procedure in the User defined function

  • 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.

  • 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?

  • 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 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.

  • 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

  • Date datatype we can't send as input parameter in function eventhough we can able to return a table.

  • 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

  • thats gr8 information

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • 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

  • 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