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:
- A Stored Procedure may or may not return a value but a function always returns a value.
- We can use a function in a Stored Procedure but we can't use a Stored Procedure in a function.
- 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
- Pre-Defined Function
- 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.