User Defined Function and Store Procedure

Question

Hello friends What is the difference between User Defined Function and Store Procedure? Thanks

Senthilkumar · Answer

I took from my article which published before 3 years in c-sharpcorner. Difference 1: Stored procedure will be used for perform specific tasks The stored procedure normally used to perform a speck task. The bulk of sql statement that that will be complied and it uses the cached execution plans. It can be return more than one result set. Normally functions will be used for computing value The functions are used to do the calculations instead of doing in the query. It can be used for many places if we want the same operation. Difference 2: Stored procedures may or may not return values The stored procedure based on query type it will do the operation. If we write any select query then it will return the results. If we do only update insert or delete then it wont return any results. However if you want to check the confirmation of the transaction then we can return the result. It is not compulsory to return the result set. But function should return value The function must return the value. Based on the function type it will return the results. If we have written scalar function then it returns single value. If we have written table valued function then it returns multiple rows. We cannot write the function without return any value to the calling program. Difference 3: Stored procedure cannot be used in the select/where/having clause The stored procedure cannot be called like the following. SELECT * FROM Pr_RetrieveEmployees -- It will throws an error It will throw an error. Similarly the stored procedure cannot be part the sql query any where. But function can be called from select/where/having clause The function can be called using the select query. It can be called from the select/where/having clause. For instance SELECT [dbo].fn_EmployeeSalary (5) Ãƒ it is scalar UDF. It returns single value. SELECT * FROM fn_EmployeeHistory (3) Ãƒ its will return multi value. Difference 4: Stored procedure can run independently . It can be executed using EXECUTE or EXEC command The stored procedure can run independently. Once the stored procedure is compiled then it can be executed. It can be executed using the sql command statement EXECUTE or EXEC. EXECUTE proc_RetrieveEmployeeDetails EXEC proc_RetrieveEmployeeDetails proc_RetrieveEmployeeDetails But function cannot run independently The function cannot run independently. It has to be the part of the SQL statement. Difference 5: Temporary table (derived) cannot be created on function. The temporary table cannot be created in the function. As you know if you create a temp table then it will be stored on the tempdb database. But the temp table won't allow us to create with inside the function There are two ways to create the temp table. 1. Create temp table 2. Derived table SELECT * INTO #tmpEmployee FROM Employees The above statement is derived table. It cannot create on function. But it can be created in stored procedures The stored procedure allows us to create the temp tables in the stored procedure. Difference 6: From sql server 2005 onwards TRY CATCH statements can be used in the stored procedures. The TRY CATCH is one of the new features in the SQL server 2005 edition. It can be used with inside the stored procedure. As you know it handles the error in the catch block whatever the statements written in the try block. But it cannot be used in the function. But we can use raise error function. The TRY CATCH block cannot be used with inside the functions. But we can use the raiserror function to throw the exception. Difference 7: Stored procedure can call the user defined functions The function can be called from the stored procedure. CREATE PROC Pr_RetirveCustomers AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON SELECT * FROM Customers SELECT * FROM [dbo].fn_GetOrderedCustomers (5) END But the function cannot call the stored procedures. The function cannot call the stored procedures like procedures. There are many types of stored procedures in sql server. System Stored procedure User defined Stored procedure NET CLR stored procedure Extended stored procedure Except extended stored procedures no one can call the user defined functions. Difference 8: Stored procedures can have input and output parameters. As you know the input and output are the parameters which can return the results through that variable. The output parameter can be only used to return the results through the output variable. But the input parameter can be do the both input and output operations. But the function can have only input parameters. This won't allow us to use the output parameters. But we can use input parameter. Difference 9: Stored procedures can have select and all DML operations. The stored procedures can do all the DML operations like insert the new record update the records and delete the existing records. But the function can do only select operation. The function won't allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions. Difference 10: Function cannot have the transaction statements. The transaction statement cannot be used in the function. Normally we won't do any DML operations in the function. Stored procedure can use transaction statements. The transaction statement can be used inside the stored procedures. Difference 11: Stored procedures can use all the data types available in sql server. The parameters for the stored procedures can be any data types which are available on the sql server. But the function cannot use the ntext image and timestamp data types as return type. The function won't allow several data types of the sql server as a parameter. Difference 12: Stored procedures can create table variable and cannot return the table variable. The table variable is one of the performances tuning mechanism. Because it takes minimum resources and it uses the memory location for store the data. (Recommended for minimum rows) It can be created and do the operations. But it cannot be the return type. But the function can create update and delete the table variable. It can return table variable. It can be created and can do all the DML operations and it can be the return type. That is called the multi valued table function. Difference 13: Stored procedure can have the dynamic sql statement and which can be executed using sp_executesql statement. The stored procedure can have the dynamic sql statement for the complex decision making operations which generated inside the stored procedures. It can be executed using the sp_executesql statement. But the function cannot execute the sp_executesql statement. The function can generate the dynamic sql statement. But it cannot get execute. It will not allow writing the sp_executesql command to execute the dynamically created sql statement. Difference 14: Stored procedure allows getdate ( ) or other non-deterministic functions can be allowed . The stored procedure will allow all the sql server built-in functions like getdate()DB_ID() DB_NAME () etc.. But the function won't allow the non-deterministic functions. The function will not allow using non-deterministic functions like GETDATE ()

Jignesh Trivedi · Answer

hi Functions ---------- 1) it can be used with Select statement 2) Not returning output parameter but returns Table variables 3) You can join User define function (UDF) 4) it Cannot be used to change server configuration 5) it Cannot be used with XML FOR clause 6) it Cannot have transaction within function 7) you cannot call SP with in function Stored Procedure ----------------- 1) you have to use EXEC or EXECUTE 2) it return output parameter 3) it can create table but won't return Table Variables 4) you can not join SP 5) it can be used to change server configuration 6) it can be used with XML FOR Clause 7) it can have transaction within SP 8) you can call function within SP hope this will help u.

Satyapriya Nayak · Answer

Hi Aditya Stored procedure A stored procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run in response to a user request it is run directly by the database engine which usually runs on a separate database server. As such it has direct access to the data it needs to manipulate and only needs to send its results back to the user doing away with the overhead of communicating large amounts of data back and forth. User-defined function A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views. In SQL Server 2000 User defined functions have 3 main categories 1.Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries 2.Inline function - can contain a single SELECT statement. 3.Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows but you can't enclose the logic for getting this rowset in a single SELECT statement. Differences between Stored procedure and User defined functions 1.UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. 2.UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. 3.Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations. 4.There will be Syntax differences and here is a sample of that. Stored procedure Code:CREATE PROCEDURE dbo.StoredProcedure1 /* ( @parameter1 datatype = default value @parameter2 datatype OUTPUT ) */ AS /* SET NOCOUNT ON */ RETURN User defined functions Code:CREATE FUNCTION dbo.Function1 ( /* @parameter1 datatype = default value @parameter2 datatype */ ) RETURNS /* datatype */ AS BEGIN /* sql statement ... */ RETURN /* value */ END Please refer the below link http://webmasterspeak.forumotion.com/t102-difference-between-stored-procedures-and-user-defined-functions Thanks