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


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.


 

Up Next
    Ebook Download
    View all
    Learn
    View all