Skip to main content

 

Differences between Stored Procedures and User Defined Functions (UDF) in SQL Server.

  • Stored procedures are   precompiled objects that can be reused. Stored procedure have many advantages:

  1. Precompiled: SQL server compiles the stored procedure once, store the execution plan and reuse it which save time to create execution plan each time the code runs.
  2. Reuse of code: Stored procedure can be used by multiple users and programs once created
  3. Security: You can restrict usage of stored procedure by defining execute permission to users.

  • User defined functions are set of codes that accept parameters, perform and action and must return a result as a scalar value or as a result set. It cannot modify data. Function encapsulate frequently performed logic, that can be called from a Select or from a where clause.
  • There are Built in functions and User defined functions. Functions has following advantages:

  1. Allows modular programming- It can be called number of times from a program and can be modified independently with out touching the entire program source code.
  2. Allow faster execution since it is precompiled.

Differences between Stored Procedure and User Defined Function in SQL Server

User Defined Function

Stored Procedure

Function must return a value.

Stored Procedure may /may not return values.

Can be used only in SELECT statements, not in DML statements.

Can be used with DML(INSERT/UPDATE/DELETE) statements  

Allow only input parameters, doesn't support output parameters.

Can have both input and output parameters.

Doesn’t allow try-catch blocks.

Allow to use Try catch blocks

Transactions are not allowed within functions.

Transactions can be implemented inside Stored Procedures.

We can use only table variables; it will not allow using temporary tables.

Can use both table variables as well as temporary table in it.

Stored Procedures can't be called from a function.

Stored Procedures can call functions.

 Can be called from SELECT statement.

Procedures can't be called from SELECT /WHERE/HAVING clauses.  

A UDF can be used in join clause  

Procedures can't be used in Join clause

 

Summary : Article describes the differences between Stored procedure and UDF.

 

Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

Error:    G etting below error while trying to copy data from one table to another: Msg 8170, Level 16, State 2, Line 1004  Insufficient result space to convert uniqueidentifier value to char.   Fix: UserID column which would be having Unique identifier data require more space (36 character). Size is specified as 15 here. To fix it just specify 36 as varchar length or specify Uniqueidentifier as data type for UserID  column.
Post a Comment
Read more

While executing Select query to pull data from a different server, SQL Server is showing following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'LAPTOP-09O6NE3K\SQLEXPRESS03' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Completion time: 2024-02-05T17:18:24.6869756+05:30 Solution: Step1:  Check if the server is available in the sys.servers table SELECT name from sys.servers Step2:  If no, add the new server to the sys.servers table EXEC sp_addlinkedserver @server = 'server name' Eg" EXEC sp_addlinkedserver @server = 'LAPTOP-09O6NE3K\SQLEXP RESS03' Step3:  Recheck if the new server name is added to the sys.servers table. If yes, try then rerunning the select query which throwed the error. Now it should pull data from the table in the new server successfully.    
Post a Comment
Read more

Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2

Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2 1.        Problem Tuning    databases so that they provide the best possible performance is one of the most important responsibilities of database administrators (DBAs).   SQL Server was originally designed at a time when it could be assumed that main memory was very expensive, so data needed to reside on disk except when it was actually needed for processing.   As the amount data    within an organization increases, accumulation of data affects the productivity of DBAs, who must spend considerable time and effort on achieving the high levels of database performance. 2.        Solution Nowadays memory is much cheaper and a working data set can be stored entirely in memory. The assumption that main memory was very expensive is no longer valid as memory prices have dropped enormously. Memory-opti...
Post a Comment
Read more