Web Analytics Made Easy - Statcounter
HomeSQL ServerImp SQL DifferenceSql Server- Difference between Stored Procedure and Function

Sql Server- Difference between Stored Procedure and Function

December 22, 2009
In Sql Server, both functions and stored procedures can be defined as the logically grouping of Sql statements formed to complete a task, but still there are many differences between Stored procedure and Functions.
These differences can be summarized as follows:-
1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.
2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement. 
3) Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function.
4) A stored procedure can return multiple parameters but a function can return only one value.
5) We can use join in the outcome from the functions but we can’t use joins in the outcome from stored procedures.
6) Transaction management is not possible in functions but it is possible in Stored procedures.
7)Print function can not be called within the function but it can be called within the stored procedure.

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

Tags:

Related Posts

About The Author

vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

6 Comments
  1. Amit Kumar

    one more main diffrence between UDF and SPROC that UDF returns table variables, while a SPROC can't return a table variable although it can create a table. Another significant difference between them is that UDFs can't change the server environment or your operating system environment, while a SPROC can.

    Reply
  2. admin

    Thanks Amit for your valuable comments and sharing your invaluable knowledge.

    Regards
    Vivek

    Reply
  3. Anonymous

    thanks vivek for sharing the articles………

    Reply
  4. pramod kumar singh

    all of above 6 differences are very minute and not Very Useful for Your Concept.all of above example comes from experience not for excellence.
    I am share our knowledge with u–in a single line of statement

    Stored procedure is Business block in Database Domain just like Method In C#,Java,C,C++ where we have put all SQL statement. on the hand in Function we have only put that statement which can't change the database structure

    Reply
    • pramod kumar singh

      means We can't use any statement that modified database structure .some are statement are as like this which cant't use in Function but u can use these statement in SP(Stored Procedure)-
      Insert,update,Delete,Create,Alter,Drop,Truncate etc.

      if i am Wrong,Let Me Know

      Reply

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading