Functions vs Stored Procedures

Stored Procedure in SQL Server

• A stored procedure is a set of SQL statements that are assigned a name and are stored for future use within multiple programs and tables. When we call a stored procedure for the first time, it is cached, so for all the subsequent times when the stored procedure is called, then the time is not wasted in repeated execution.
• Stored procedures are the reusable units that encapsulate the logical statements in SQL. They are stored in the database dictionary.
• Stored procedures can be thought of as subroutines or methods in other programming languages that offer a variety of advantages including speed and efficiency.
• Stored procedures provide maintainability i.e. they provide one location to store tricky code so instead of having to replicate a series of program steps at several locations of a database, we can put them within a stored procedure and then call that particular stored procedure which encourages modular programming.
• The stored procedure provides efficiency with the logic of being stored on the server as we can avoid some network traffic. In contrast to having sent multiple commands from a client to a database, we can call a stored procedure.

Syntax of a stored procedure

Create Procedure Procedure_name

As

Sql_statement

Go;

Advantages of a stored procedure

Stored Procedures offer a number of benefits which are as follows:

1. Reusability

Stored procedures are capable of using an execution plan. Whenever, we issue a query three things happen i.e. checking the syntax of the query, compiling the query, and generating an execution plan. An execution plan refers to the best possible way with which the query is retrieved from the database. In case of stored procedures, since the execution plan is already generated it will be reused by the stored procedure as the execution plan would have been cached to the SQL server.

2. Improved Performance

As the execution plan is already cached on the server, this reduces the network traffic thereby enhancing the performance of the application. In case of the stored procedure, we just need to call the stored procedure but if we are not using a stored procedure, then we have to execute a large number of ad hoc queries that contribute to network traffic.

3. Easier Maintainability

If there is a bug in our application, then we just need to check the logic in the stored procedure in spite of being used by multiple applications, we need not make changes everywhere. Thus, we can say stored procedures provide better maintainability.

4. Security

Stored procedures avoid SQL injection attacks. Building SQL statements dynamically by concatenating strings can cause SQL injection. But it can be prevented by using stored procedures.

Disadvantages of a stored procedure

Stored procedures also have certain drawbacks which are as follows:

1. Debugging

Debugging is really difficult in case of stored procedures. And the debugging capabilities of stored procedures vary from server to server in a relational database management system.

2. Portability

Versioning is another important feature that stored procedures don’t support easily. Complex stored procedures may also not port to the upgraded versions.

3. Testability

Testing of the logic embedded in a stored procedure is complex. Data errors are not generated until runtime.

4. Cost

A database administrator is required to manage and maintain the complex and large stored procedures depending on the structure of the organization, which incurs cost.

Functions in SQL Server

• Functions in SQL are very much similar to the functions in any other programming language. Functions are the subroutines or methods that perform a specific task and returns the result.
• Functions in SQL are of various types like system function, user-defined functions, scalar functions, and table-valued functions.
• A user-defined function is a module of code that takes input and produces the output in the form of tables, rows or a subset of rows from the database.
• A function may or may not have parameters but it should return values.
• We can also say that a user-defined function is a name given to a block of statements that accepts some input and necessarily provides an output.

Syntax of a user-defined functions

Function Function_name (@Parameter_Name Data_type,…….@Parameter_Name Data_type )

Returns Data_type

As

BEGIN

Function Body returns data

END

Advantages of User-defined functions in SQL Server

1. User-defined functions can increase efficiency with their ability to be used with clauses like ‘where’, ‘select’ or ‘case’ statements which help in filtering out data with ease. Also, they can be used to create joins.
2. User-defined functions promote a modular programming approach as they are defined and stored under a block and can be invoked any number of times within the program. Also, they can be modified independently without any interference from the program.
3. The execution is faster in functions. Just like stored procedures the execution plans are cached which results in faster execution and increases efficiency.
4. A user-defined function may return a scalar value or it can also return a table.

Disadvantages of User-defined functions in SQL Server

1. User-Defined functions take less number of the parameter in comparison to stored procedures. UDFs can take up to 1024 parameters whereas stored procedures can have up to 21000 parameters.
2. User-Defined functions can return only one-row set to the user whereas stored procedures can return multiple row sets.
3. UDFs cannot use non-deterministic built-in functions. Non-deterministic functions are the ones which return different result different results every time even when the same input is supplied. Thus, we cannot use GETDATE() in UDFs.
4. We cannot use DML statements like INSERT, UPDATE, DELETE within the user-defined functions.

Difference between Stored Procedures and Functions