none
Difference between procedure and function ?

Answers

  • here my findings Smile

     

    Features

    Procedures

    Functions

    Parameters

    Supports in, out, in & out

    Only supports in

    Temp Object

    Accessible – You can use the temp tables inside the procedure

    Not supported

    Create as Temp

    Accepted –

    Create Proc #MyProc..

    Not supported

    Select Result

    Supported

    Not supported

    Return

    Return integer value

    Returns any type of value

    On DML Quires

    Not allowed

    You can embed the function on query

    Calling SPs

    Allowed

    Not Allowed

    Calling Another Functions

    Allowed

    Allowed

    Insert/Update/Delete

    Allowed

    Not Allowed

    Or

    Only allowed against the table variables

    Recursive Operation

    Allowed

    Allowed

    Versioning (grouped)

    Allowed

    Not Allowed

    Schema Binding

    Not Allowed

    Allowed

    Creating Objects

    Allowed

    Not Allowed

    EXEC

    Allowed

    Not Allowed

    SP_EXECUTESQL

    Dynamic SQL

    Allowed

    Not Allowed

    GETDATE() or other non-deterministic functions

    Allowed

    Not Allowed

    SET OPTION

    Not Allowed

    Allowed

    Setting Permission

    Grant/Deny

    Yes

    No for scalar functions.

    Yes for Table Values/Inline Functions

    Monday, July 16, 2007 8:35 AM
  • Hi Sahara,

     

    Please read the BOL for more information about them. You can find the answer in the following sections:

     

    Stored procedures: 

    Designing and Creating Databases > Stored Procedures (Database Engine) > Understanding Stored Procedures >

     

    Functions:

    Designing and Creating Databases > User-defined Functions (Database Engine) >

     

     Regards,

     

    Janos

    Monday, July 16, 2007 6:19 AM

All replies

  • Hi Sahara,

     

    Please read the BOL for more information about them. You can find the answer in the following sections:

     

    Stored procedures: 

    Designing and Creating Databases > Stored Procedures (Database Engine) > Understanding Stored Procedures >

     

    Functions:

    Designing and Creating Databases > User-defined Functions (Database Engine) >

     

     Regards,

     

    Janos

    Monday, July 16, 2007 6:19 AM
  • here my findings Smile

     

    Features

    Procedures

    Functions

    Parameters

    Supports in, out, in & out

    Only supports in

    Temp Object

    Accessible – You can use the temp tables inside the procedure

    Not supported

    Create as Temp

    Accepted –

    Create Proc #MyProc..

    Not supported

    Select Result

    Supported

    Not supported

    Return

    Return integer value

    Returns any type of value

    On DML Quires

    Not allowed

    You can embed the function on query

    Calling SPs

    Allowed

    Not Allowed

    Calling Another Functions

    Allowed

    Allowed

    Insert/Update/Delete

    Allowed

    Not Allowed

    Or

    Only allowed against the table variables

    Recursive Operation

    Allowed

    Allowed

    Versioning (grouped)

    Allowed

    Not Allowed

    Schema Binding

    Not Allowed

    Allowed

    Creating Objects

    Allowed

    Not Allowed

    EXEC

    Allowed

    Not Allowed

    SP_EXECUTESQL

    Dynamic SQL

    Allowed

    Not Allowed

    GETDATE() or other non-deterministic functions

    Allowed

    Not Allowed

    SET OPTION

    Not Allowed

    Allowed

    Setting Permission

    Grant/Deny

    Yes

    No for scalar functions.

    Yes for Table Values/Inline Functions

    Monday, July 16, 2007 8:35 AM
  • This listing is really great. It helps so much.

    Thanks,
    Vinod

    Tuesday, September 21, 2010 4:31 AM