What is Stored Procedure And Function How to write In Sql Server

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 @val varchar(30)
select @val =name from student where sid=@id 

return @val  

end
Calling the function:
declare @sname varchar(30)
exec @sname=funName 104
print @sname

Output: mithilesh

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:-
->Stored procedure may or may not return a value that value is only integer type,but function return the values of any data type. 

->We can use out parameter in stored procedure but not with function. 

->Stored procedure is compile once when we created but function is always recompile whenever we call it.
->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.
->Stored procedure can not be called within the function but a function is called within a Stored procedure.
->Exception handling can be done in Stored procedure but not in function.
->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.

Views In Microsoft SQL Server

A View is a logical or virtual table,which does not exists physically in the database.View is an object which contains'select' statement.View is consider like as Virtual table.
We create a view for two purpose.
Security region --> If we don't want to show all the column's data of the table to an user then we generally create a view.

Example:-

Create view with two tables:- I am going to create a view with the help of joining of two tables.
create view vdata(student_id,student_name,student_course)
as
select sid,sname,cname from scourse c join studentdetails s
on c.id=s.sid
Output:-

Create view with Left Outer Join:-

create view vdata1(student_id,student_name,student_course)
as
select sid,sname,cname from scourse c Left join studentdetails s
on c.id=s.sid

Create view with Right Outer Join:-
create view vdata2(student_id,student_name,student_course)
as
select sid,sname,cname from scourse c Right join studentdetails s
on c.id=s.sid

Create view with Full Outer Join:-
create view vdata3(student_id,student_name,student_course)
as
select sid,sname,cname from scourse c full outer join
studentdetails s on c.id=s.sid
Output:-

To get the data through view:-
Syntax:-
select *from view_name
Ex.
select*from vdata
To get the details of view:-
sp_help vdata
sp_helptext vdata