In Focus

Function in SQL Server 2012

In this article I described functions in SQL Server.

Introduction

In this article I described how to create a function in SQL Server, how to execute a function, the deferences between functions and Stored Procedures and the use of functions in Stored Procedures.

Function

A function is a sequence of statements that accepts input, processes them to perform a specific task and provides the output.

Difference between function and Stored Procedure:

  1. A Stored Procedure may or may not return a value but a function always returns a value.
  2. We can use a function in a Stored Procedure but we can't use a Stored Procedure in a function.
  3. We can't use the result of a Stored Procedure in a WHERE or SELECT list, while there is not this type of restriction for a function.

Type of function

  1. Pre-Defined Function
  2. User-Defined Function

Here I describe only User-defined functions.

User-defined Function:

In a user-defined function we write our logic according to our needs. The main advantage of a user-defined function is that we are not just limited to pre-defined functions. We can write our own functions for our specific needs or to simplify complex SQL code. The return type of a SQL function is either a scalar value or a table.

First of all I create a table for which we create the function.

Creation of the table:

create table item(itemId int,itemName varchar(15),itemCost int)


Insertion of data:

insert into item

select 1,'a',100union all

select 2,'b',200union all

select 3,'c',300union all

select 4,'d',150


Output:

select * from item


function-in sql-server.jpg

Creation of function:

create
 function ss(@id int)

returns table

as

return select * from item where itemId=@id


Execution of Function:

select * from ss(1)

Output:

function-in sql-server-fun.jpg

We can use a function in a Storedc Procedure.

Creation of Stored Procedure:

create
 proc usp_funn(@item_Id int)

as

select * from ss(@item_Id) 


Execution of Stored Prtocedure:

exec
 usp_funn 2

Output:

function-in sql-server-fun-store-procedure.jpg

Summary:

In this article I described functions in SQL Server. I hope this article has helped you in understanding this topic. Please share it. If you know more about this, your feedback and constructive contributions are welcome.