A Closer Look Over Function & Stored Procedure in SQL Server

Function and Stored Procedure are two very different entities in SQL Server, you may already familiar with these two commonly used terms.

This article briefly explain What is a Stored Procedure and what is a Function in SQL Server? It also explains the working and major differences between function and stored procedure in SQL server.

What is Function in SQL Server

SQL Server gives the feature to define the functions. In simple words, we can say that, the function is a utility that group the several statements, performs a task and return a value as actions. Value can either be a single scalar value or a result set.

Types of Functions in SQL Server

There are two types of functions in SQL which are given as following:

1. Built-in Functions
2. User-Defined Funtion

Built-in Functions

The built-in functions are facility provided by the SQL Server to perform several tasks. These functions can also used in T-SQL statements. The built-in function accesses the information from SQL Server system tables without accessing the system tables directly. The built-in functions return value either scalar or table data types.

Types of Built-in Functions

MS SQL provides four types of built-in functions, but the two main functions of are Scalar Function and Aggregate Function that can be used in SQL statements to perform certain tasks.

1. Scalar Function

The scalar function works on a single valued statement and returns only NULL value or a single value. Here some useful SQL Server Scalar functions are described below.

abs(): this function returns, absolute number of the given values.
rand(): returns a random value.
lower(): returns the lower case letter of the given statement.
upper (): returns the upper case letter of the given statement.
round(): returns round off value of a given statement.


2. Aggregate Function:

The aggregate functions are those functions which are operated on a set of values and returns a single value. Here some useful SQL Server aggregate functions are listed below:

max(): returns the maximum value for multiple values.
min(): returns the minimum value from the given values.
avg(): returns the average from the set of values.
count(): returns the number of counts from the set of values etc.

3. Rowset Function

Rowset function is used to return an object that can be used like table references in SQL Statements. This function is a different technique to access the tables in a linked server. The rowset function can be referenced using FROM clause of a query.

Rowset function does not accept variables for its arguments.
E.g.- containstable(), openquery(), freetexttable(), opendatasource() etc..
4. Ranking function

This type of function is used to return the ranking value for each row a partition. Here some examples of ranking functions are given following:

E.g.- rank(), denserank(), ntile(), rownumber() etc.

User Defined Function

The user defined functions or subroutines performs actions like complex calculation and return results. These functions are made up of one or more T-SQL statements and can be used as to encapsulate the codes for reuse.

In the SQL databases, a user defined function provides a procedure for increasing the functionality of the server by adding a function that can be evaluated in SQL statements. User-defined functions are created using the Create Function statement. Once a user defined function is created, then it can be used in any SQL Statement.

Syntax:

CREATE FUNCTION function_name( [ {parameter_name [ AS ][ type_schema_name. ]
parameter_data_type [ = default ] [ READONLY ] }
[ ,..n ] ] ) RETURNS return_data_type [ WITH [ ,..n ] ] [ AS ] BEGIN function_body
RETURN scalar_expression
END

Types of User Defined Functions

There are three types of user defined functions in SQL, these are given as following:

1. Scalar user defined function:

A Scalar user defined function can accept from 0 to many input parameters and a single value will be returned as a result. A scalar UDF can return the results in the form of int, char, and varchar etc.. The scalar user defined function does not supports text, ntext functions etc..

Once the function created we can use it as like this.

PRINT dbo.AddTwoNumbers

2. Inline table valued user defined function:

An inline table-valued user defined function produces data type table variables and its value can be derived from the single select statement. In this function there is no need of begin and end blocks, no need to specify the variables of the tables and not allowed duplicate column names etc.. After declaration of function we can use it as like this.

SELECT * FROM GetAuthorsByState('CA')

3. Multi Statement table valued user defined function:

This is a different type of function which may contain multiple T-SQL statements. When you want to create a multi statement table valued function then you must need to define the table structure. We can use it in the FROM clause of a T-SQL command.

After declaration of multi statements table valued user defined function as like this.

SELECT * FROM GetAuthorsByState('CA') SELECT * FROM GetAuthorsByState('XY')

Limitations of User Defined Functions:

• Unable to modify the databases states.
• Does not contains OUTPUT INTO clause.
• A UDF can’t support TRY.. ..CATCH and SET statements.
• Unable to call a stored procedure (SP), but can call an extended stored procedure(ESP).
• Can not use dynamic SQL or temp tables, while table variables may use.
• XML clause is not allowed in user defined functions.

What is Stored Procedure in SQL Server

A stored procedure (SP), is a combination of SQL statements that contains logical statements to perform a particular task. The Stored Procedures are also used to encapsulate the set of queries that perform on a server. The main advantage of using stored procedures in SQL Server statements is that it reduces the consistencies of the database. We don't use this prefix when naming user-defined procedures.

Types of Stored Procedures in SQL Server

There are several four type of stored procedures in SQL. Here I have explained broadly each of them:

1. User-defined Stored Procedure

User-defined Stored Procedure is created by the user for their personal use. These type of stored procedure can be developed using T-SQL or you can use the CLR (Common Language Runtime) method to design the stored procedures.

2. System-defined Stored Procedure:

System-defined stored procedures is predefined in SQL Server. These stored procedures are stored in SQL Server Resource Database and logically appear in the system schema of user defined and system defined databases. The system defined stored procedures starts with the syntax of sp_ prefix. Here is a list of some useful system defined procedure.

Here some examples of System Stored procedures are given as follows:

sp_depends - provide details of all database objects that depends on the specific database object.
sp_rename - rename a database object like stored procedures, views, table etc.
sp_help – provides the details on any database object.
sp_helptext - provides the help text of a stored procedure reside in SQL Server.
sp_changeowner - changes the owner of a database object.
sp_helpdb – gives the details of the databases defined in the SQL Server.

3. Temporary Stored Procedure:

The temporary stored procedure means that it is present in temporary session. This type of stored procedures is prefixed with a hash sign (# ). Where one sign shows that it is present in the temporary session while if it used with double hash sign, this means that the procedure is present globally.

4. Extended Stored Procedure:

Extended stored procedures exist because they allow you to do things that you cannot do with T-SQL like running a DOS command (xp_cmdshell). These stored procedures are routines resides in DLLs which are very similar to the regular stored procedures. The extended stored procedures receives the parameters and return resultant value via SQL Server's Open Data Services API.

Limitations of Stored Procedure

There are also some limitations of stored procedure in SQL which are discussed below:

• Stored procedure may or not return values.
• A stored procedure can’t be utilized in selective statements.

Conclusion

In this blog post we have discussed about the function and stored procedure in SQL Server. We have also discussed about the limitations of the function and stored procedure in SQL Server.