Writing Functions and Stored Procedures in SQL Server

A collection of SQL statements is contained in stored procedures and functions, database objects used to carry out certain tasks (or may also be used in data science). Both are distinct from one another in a variety of ways.

In this article, we will be discussing functions and procedures in detail, along with their differences.

Let us start with stored procedures −

Stored Procedure in SQL

Simply written SQL code saved for reuse several times makes up a stored procedure. If you can think of a query that you write often, you could save it as a stored procedure and then call the stored procedure to run the SQL code that you saved as part of the stored procedure. This would save you from having to write the same question repeatedly.

You may execute the same SQL code repeatedly and supply parameters to the stored procedure as well. Depending on the needs, the stored procedure will respond appropriately based on the parameter values supplied.

Performance can also be enhanced via stored procedures. A set of SQL statements are used to implement several tasks. Which following SQL statements are run depending on the outcomes of the initial SQL statements and conditional logic. These SQL statements and the conditional logic they contain may be combined into a single execution plan on the server by writing them into a stored procedure. The conditional logic may be executed without delivering the results to the client since all the work is carried out on the server.

Advantages of stored procedures

Compilation and execution

Each Stored Procedure is once compiled by SQL Server, which then reuses the execution plan. When Stored Procedures are often invoked, the performance gains are enormous.

Client/server traffic has decreased

You'll be relieved to know that Stored Procedures may condense lengthy SQL searches into a single line that can be carried over the wire if network bandwidth is an issue in your environment.

Code reuse that is effective and programming abstraction

Numerous users and client applications can use stored procedures. It will take less time to complete the development cycle if you use them in a planned method.

Heightened security measures

Independent of the permissions for the underlying tables, you can provide users access to run stored procedures.

Functions in SQL

There are 2 types of functions that SQL Servers support

Built-in functions

Built-in functions operate following the Transact-SQL Reference definitions and are unchangeable. Only Transact-SQL statements that follow the Transact-SQL Reference's established syntax may use the functions as references.

The system has already defined these functions. It is separated into two categories −

In this tutorial we will be referring to the following table −

ID	Name	Marks	Age
1	Harsh	90	19
2	Suresh	50	20
3	Pratik	80	21
4	Dhanraj	95	19
5	Ram	85	18

Scalar Function

These operations take one value as input and output it. Several System Scalar operations include −

• round() − rounds a number off to the nearest three places. For instance, round(28.64851) will yield 28.649

SELECT ROUND(MARKS,0) FROM students;

• upper() − upper("english") returns ENGLISH and lower("ENGLISH") returns english.

SELECT upper(NAME) FROM Students;

Output

HARSH
SURESH
PRATIK
DHANRAJ
RAM

• rand() − Using the function rand(), a random number within a range will be returned. for instance, Rand(8),returns 0.71372242401 or any other randomly generated number.

System Aggregate Function

A single value is returned by these functions, which work with a collection of input arguments. Examples include −

Avg() will provide the average value for all provided inputs.

Example

SELECT AVG(MARKS) FROM Students;

Output

80

Count() This function will return the number of rows that meet the given criteria.

Example

SELECT COUNT(*) FROM Students;

Output

5

Max() and min() The functions max() and min() will return the highest and lowest value among the provided parameters.

Example

SELECT MAX(AGE) FROM Students

Output

21

Example

SELECT MIN(AGE) FROM Students;

Output

18

User Defined Functions

Use the CREATE FUNCTION command to create custom Transact-SQL functions. User Defined Functions deliver a single value and need zero to many input arguments. A single data value, such as a decimal number, char, or an int, is what certain user-defined functions (UDFs) return.

Scalar Operation

User-defined scalar functions output a single value for each step of the function's operations. Any data type value from the function is returned.

Table-Valued Functions

Inline Functions

Inline tables with user-defined values Function operates and returns the result as a table. There isn't a BEGIN/END body. To get the outcome, just one SELECT statement is used.

Multiple Statement Function

The result given by a user-defined function is not changed if it contains a SELECT statement that cannot be modified or contains several SELECT statements. We must explicitly specify table variables and describe the value that may be retrieved from various SQL queries.

Advantages of User-Defined Functions

• Modular programming is supported

• The function may be created once, saved in the database, and then used many times as you like in your software. The source code of the application need not be changed to change User Defined Functions.

• They enable quicker execution

• Transact-SQL User Defined Functions, like Stored Procedures, lower the compilation cost by caching the plans and reusing them for many executions. This results in substantially quicker execution times since the user-defined function is not required to be reparsed and optimised upon each usage.

• For computational workloads, business logic, and string manipulation, CLR functions outperform Transact-SQL functions significantly. Data-access-intensive logic is better suited for transact-SQL operations.

• They might lessen network activity.

• A function can be used to represent an action that filters information based on a complicated constraint that can't be stated in a single numerical expression. To decrease the number of rows given to the client, the function may then be used in the WHERE clause.

Difference between user-defined functions and stored procedure

The following table highlights the major differences between the user defined functions and stored procedure in SQL −

Criteria	User-defined functions	Stored Procedure
Return value	Single value	Single,multiple or even zero
Parameters	Input values	Input and Output values
Database	Cannot modify	Can modify
Statements	Only SELECT statement	Both SELECT AND DML statements
Calling	Called from procedure	Cannot be called from function
Compilation and execution	Needs compilation every time	Needs compilation only once
Transaction management	Not possible	Not possible

Conclusion

In this article, we thoroughly discussed stored procedures with its advantages, Functions, types of functions and advantages of functions and finally concluded with the difference between Functions and stored procedures.