Stored procedure and Function in MS Sql Server

In this tutorial i will tell you"what is difference between stored procedure and function".But first i will tell you what is function and why we use function.
Function:-Function may contain a set of statement as stored procedure but generally we create function if there is some calculations which we can do frequently.
Example:

Create a function:

create function funName(@id int)
returns varchar(30)
as
begin declare @n varchar(30)
select @n=name from student where sid=@id
return @n
end

Calling the function:

declare @sname varchar(30)
exec @sname=funName 104
print @sname

Output:

Description:-
Here first i have created a student table here .After that i have created a function funName(@id int) which takes one parameter. Then i have called the function and passed the parameter value(exec @sname=funName 104).

Difference between Stored procedure and function:-

There are following difference between stored procedure and Function which is given below:

1. Stored procedure may or may not return a value that value is only integer type,but function return the values of any data type.
2. We can use out parameter in stored procedure but not with function.
3. Stored procedure is compile once when we created but function is always recompile whenever we call it.
4. Within a Stored procedure we can write such statement which can affect the database or can be time dependent(for example DML statement).But such statement can not return within function.
5. Stored procedure can not be called within the function but a function is called within a Stored procedure.
6. Exception handling can be done in Stored procedure but not in function.
7. Within a Stored procedure we can write such statement which will display data directly to the user.But In function we can not write such SELECT Statement.

For More:-

1. Ms Sql server
2. Data Intigrity
3. Create table and insert Data
4. Setup File
5. oops concepts
6. File Handling Real Application

I hope this is helpful for you.

To Get the Latest  Free Updates Subscribe
click below for download codes
       Download