Function vs Stored Procedure in SQL Server

Question

I ve been learning Functions and Stored Procedure for quite a while but I don t know why and when I should use a function or a stored procedure  They look same to me  maybe because I am kinda newbie about that   Can some one tell me why

User · Answer

Generally using stored procedures is better for perfomances. For example in previous versions of SQL Server if you put the function in JOIN condition the cardinality estimate is 1 (before SQL 2012) and 100 (after SQL 2012 and before of SQL 2017) and the engine can generate a bad execution plan.

Also if you put it in WHERE clause the SQL Engine can generate a bad execution plan.

With SQL 2017 Microsoft introduced the feature called interleaved execution in order to produce a more accurate estimate but the stored procedure remains the best solution.

For more details look the following article of Joe Sack https://techcommunity.microsoft.com/t5/sql-server/introducing-interleaved-execution-for-multi-statement-table/ba-p/385417

User · Answer

Stored Procedures are used as scripts  They run a series of commands for you and you can schedule them to run at certain times  Usually runs multiples DML statements like INSERT  UPDATE  DELETE  etc  or even SELECT   Functions are used as methods  You pass it something and it returns a result  Should be small and fast - does it on the fly  Usually used in a SELECT statement

User · Answer

SQL Server functions  like cursors  are meant to be used as your last weapon  They do have performance issues and therefore using a table-valued function should be avoided as much as possible  Talking about performance is talking about a table with more than 1 000 000 records hosted on a server on a middle-class hardware  otherwise you don t need to worry about the performance hit caused by the functions    Never use a function to return a result-set to an external code  like ADO Net  Use views stored procs combination as much as possible  you can recover from future grow-performance issues using the suggestions DTA  Database Tuning Adviser  would give you  like indexed views and statistics  --sometimes    for further reference see  http   databases aspfaq com database should-i-use-a-view-a-stored-procedure-or-a-user-defined-function html

User · Answer

The difference between SP and UDF is listed below      Stored Procedure  SP  Function  UDF - User Defined      SP can return zero  single or multiple values  Function must return a single value  which may be a scalar or a table     We can use transaction in SP  We can t use transaction in UDF    SP can have input output parameter  Only input parameter    We can call function from SP  We can t call SP from function    We can t use SP in SELECT  WHERE  HAVING statement  We can use UDF in SELECT  WHERE  HAVING statement    We can use exception handling using Try-Catch block in SP  We can t use Try-Catch block in UDF

User · Answer

STORE PROCEDURE                 FUNCTION  USER DEFINED FUNCTION         Procedure can return 0  single or       Function can return only single value       multiple values                                                                   Procedure can have input  output        Function  can have only input     parameters                              parameters                                                       Procedure cannot be called from         Functions can be called from     function                                procedure                                              Procedure allows select as well as      Function allows only select statement     DML statement in it                     in it                                              Exception can be handled by             Try-catch block cannot be used in a     try-catch block in a procedure          function                                              We can go for transaction management    We can not go for transaction     in procedure                            management in function                                              Procedure cannot be utilized in a       Function can be embedded in a select     select statement                        statement                                              Procedure can affect the state          Function can not affect the state     of database means it can perform        of database means it can not        CRUD operation on database              perform CRUD operation on                                             database                                               Procedure can use temporary tables      Function can not use                                             temporary tables                                               Procedure can alter the server          Function can not alter the      environment parameters                  environment parameters                                                 Procedure can use when we want          Function can use when we want    instead is to group a possibly-         to compute and return a value    complex set of SQL statements           for use in other SQL                                             statements

User · Answer

Functions and stored procedures serve separate purposes  Although it s not the best analogy  functions can be viewed literally as any other function you d use in any programming language  but stored procs are more like individual programs or a batch script   Functions normally have an output and optionally inputs  The output can then be used as the input to another function  a SQL Server built-in such as DATEDIFF  LEN  etc  or as a predicate to a SQL Query - e g   SELECT a  b  dbo MyFunction c  FROM table or SELECT a  b  c FROM table WHERE a   dbo MyFunc c    Stored procs are used to bind SQL queries together in a transaction  and interface with the outside world  Frameworks such as ADO NET  etc  can t call a function directly  but they can call a stored proc directly   Functions do have a hidden danger though  they can be misused and cause rather nasty performance issues  consider this query   SELECT   FROM dbo MyTable WHERE col1   dbo MyFunction col2    Where MyFunction is declared as   CREATE FUNCTION MyFunction   someValue INTEGER  RETURNS INTEGER AS BEGIN    DECLARE  retval INTEGER     SELECT localValue        FROM dbo localToNationalMapTable       WHERE nationalValue    someValue     RETURN  retval END   What happens here is that the function MyFunction is called for every row in the table MyTable  If MyTable has 1000 rows  then that s another 1000 ad-hoc queries against the database  Similarly  if the function is called when specified in the column spec  then the function will be called for each row returned by the SELECT   So you do need to be careful writing functions  If you do SELECT from a table in a function  you need to ask yourself whether it can be better performed with a JOIN in the parent stored proc or some other SQL construct  such as CASE     WHEN     ELSE     END

User · Answer

To decide on when to use what the following points might help-   Stored procedures can t return a table variable where as function can do that  You can use stored procedures to alter the server environment parameters where as using functions you can t    cheers

User · Answer

In SQL Server  functions and stored procedure are two different types of entities    Function  In SQL Server database  the functions are used to perform some actions and the action returns a result immediately  Functions are two types    System defined  User defined   Stored Procedures  In SQL Server  the stored procedures are stored in server and it can be return zero  single and multiple values  Stored Procedures are two types    System Stored Procedures User Defined Procedures

User · Answer

Functions are computed values and cannot perform permanent environmental changes to SQL Server  i e   no INSERT or UPDATE statements allowed    A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set   A point worth noting from comments  which summarize the answer  Thanks to  Sean K Anderson      Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters    the arguments   Functions are not allowed to change anything  must   have at least one parameter  and they must return a value  Stored   procs do not have to have a parameter  can change database objects    and do not have to return a value

User · Answer

User Defined Function    Function must return a value  Will allow only Select statements  it will not allow us to use DML statements  It will allow only input parameters  doesn t support output parameters  It will not allow us to use try-catch blocks  Transactions are not allowed within functions  We can use only table variables  it will not allow using temporary tables  Stored Procedures can t be called from a function  Functions can be called from a select statement  A UDF can be used in join clause as a result set    Stored Procedure   Stored Procedure may or not return values  Can have select statements as well as DML statements such as insert  update  delete and so on It can have both input and output parameters  For exception handling we can use try catch blocks  Can use transactions within Stored Procedures  Can use both table variables as well as temporary table in it  Stored Procedures can call functions  Procedures can t be called from Select Where Having and so on statements  Execute Exec statement can be used to call execute Stored Procedure  Procedures can t be used in Join clause

User · Answer

Basic Difference Function must return a value but in Stored Procedure it is optional  Procedure can return zero or n values   Functions can have only input parameters for it whereas Procedures can have input output parameters   Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters   Functions can be called from Procedure whereas Procedures cannot be called from Function  Advance Difference Procedure allows SELECT as well as DML INSERT UPDATE DELETE  statement in it whereas Function allows only SELECT statement in it  Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement  Stored Procedures cannot be used in the SQL statements anywhere in the WHERE HAVING SELECT section whereas Function can be  Functions that return tables can be treated as another rowset  This can be used in JOINs with other tables  Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations  Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function  We can go for Transaction Management in Procedure whereas we can t go in Function  source

User · Answer

It is mandatory for Function to return a value while it is not for stored procedure  Select statements only accepted in UDF while DML statements not required  Stored procedure accepts any statements as well as DML statements  UDF only allows inputs and not outputs  Stored procedure allows for both inputs and outputs  Catch blocks cannot be used in UDF but can be used in stored procedure  No transactions allowed in functions in UDF but in stored procedure they are allowed  Only table variables can be used in UDF and not temporary tables  Stored procedure allows for both table variables and temporary tables  UDF does not allow stored procedures to be called from functions while stored procedures allow calling of    functions  UDF is used in join clause while stored procedures cannot be used in join clause  Stored procedure will always allow for return to zero  UDF  on the contrary  has values that must come -      back to a predetermined point

User · Answer

Functions can be used in a select statement where as procedures cannot  Stored procedure takes both input and output parameters but Functions takes only input parameters  Functions cannot return values of type text  ntext  image  amp  timestamps where as procedures can      Functions can be used as user defined datatypes in create table but  procedures cannot       Eg -create table  lt tablename gt  name varchar 10  salary getsal name    Here getsal is a user defined function which returns a salary type  when table is created no storage is allotted for salary type  and getsal function is also not executed  But when we are fetching some values from this table  getsal function get   s executed and the return Type is returned as the result set

User · Answer

a User Defined Function is an important tool available to a sql server programmer  You can use it inline in a SQL statement like so  SELECT a  lookupValue b   c FROM customers    where lookupValue will be an UDF  This kind of functionality is not possible when using a stored procedure  At the same time you cannot do certain things inside a UDF  The basic thing to remember here is that UDF s    cannot create permanent changes cannot change data    a stored procedure can do those things    For me the inline usage of a UDF is the most important usage of a UDF

User · Answer

Write a user-defined function when you want to compute and return a value for use in other SQL statements  write a stored procedure when you want instead is to group a possibly-complex set of SQL statements  These are two pretty different use cases  after all

User · Answer

Mssql stored procedure vs function

User · Answer

Start with functions that return a single value   The nice thing is you can put frequently used code into a function and return them as a column in a result set   Then  you might use a function for a parameterized list of cities   dbo GetCitiesIn  NY    That returns a table that can be used as a join   It s a way of organizing code   Knowing when something is reusable and when it is a waste of time is something only gained through trial and error and experience   Also  functions are a good idea in SQL Server   They are faster and can be quite powerful   Inline and direct selects   Careful not to overuse

User · Answer

Here s a practical reason to prefer functions over stored procedures   If you have a stored procedure that needs the results of another stored procedure  you have to use an insert-exec statement   This means that you have to create a temp table and use an exec statement to insert the results of the stored procedure into the temp table   It s messy   One problem with this is that insert-execs cannot be nested   If you re stuck with stored procedures that call other stored procedures  you may run into this   If the nested stored procedure simply returns a dataset  it can be replaced with a table-valued function and you ll no longer get this error    this is yet another reason we should keep business logic out of the database

User · Answer

I realize this is a very old question  but I don t see one crucial aspect mentioned in any of the answers  inlining into query plan   Functions can be      Scalar   CREATE FUNCTION     RETURNS scalar type AS BEGIN     END Multi-statement table-valued   CREATE FUNCTION     RETURNS  r TABLE      AS BEGIN     END Inline table-valued   CREATE FUNCTION     RETURNS TABLE AS RETURN SELECT       The third kind  inline table-valued  are treated by the query optimizer essentially as  parametrized  views  which means that referencing the function from your query is similar to copy-pasting the function s SQL body  without actually copy-pasting   leading to the following benefits    The query planner can optimize the inline function s execution just as it would any other sub-query  e g  eliminate unused columns  push predicates down  pick different JOIN strategies etc    Combining several inline function doesn t require materializing the result from the first one before feeding it to the next    The above can lead to potentially significant performance savings  especially when combining multiple levels of functions     NOTE  Looks like SQL Server 2019 will introduce some form of scalar function inlining as well

User · Answer

Stored procedure    Is like a miniature program in SQL Server  Can be as simple as a select statement  or as complex as a long script that adds  deletes  updates  and or reads data from multiple tables in a database   Can implement loops and cursors  which both allow you to work with smaller results or row by row operations on data   Should be called using EXEC or EXECUTE statement  Returns table variables  but we can t use OUT parameter  Supports transactions    Function    Can not be used to update  delete  or add records to the database  Simply returns a single value or a table value  Can only be used to select records  However  it can be called very easily from within standard SQL  such as   SELECT dbo functionname  Parameter1     or  SELECT Name  dbo Functionname  Parameter1   FROM sysObjects  For simple reusable select operations  functions can simplify code  Just be wary of using JOIN clauses in your functions   If your function has a JOIN clause and you call it from another select statement that returns multiple results  that function call will JOIN those tables together for each line returned in the result set  So though they can be helpful in simplifying some logic  they can also be a performance bottleneck if they re not used properly  Returns the values using OUT parameter  Does not support transactions

User · Answer

Differences between stored procedures and user-defined functions    Stored procedures cannot be used in Select statements   Stored procedures support Deferred Name Resolution   Stored procedures are generally used for performing business logic   Stored procedures can return any datatype   Stored procedures can accept greater numbers of input parameter than user defined functions   Stored procedures can have up to 21 000 input parameters   Stored procedures can execute Dynamic SQL   Stored procedures support error handling  Non-deterministic functions can be used in stored procedures       User-defined functions can be used in Select statements  User-defined functions do not support Deferred Name Resolution  User-defined functions are generally used for computations  User-defined functions should return a value  User-defined functions cannot return Images  User-defined functions accept smaller numbers of input parameters than stored procedures   UDFs can have up to 1 023 input parameters  Temporary tables cannot be used in user-defined functions   User-defined functions cannot execute Dynamic SQL  User-defined functions do not support error handling   RAISEERROR OR   ERROR are not allowed in UDFs  Non-deterministic functions cannot be used in UDFs   For example  GETDATE   cannot be used in UDFs

[sql] Function vs. Stored Procedure in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to sql-function