Function in SQL Server 2012

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

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:



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:



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.