STORE PROCEDURE AND FUNCTION

 STORE PROCEDURE :-

  Store Procedure is a reusability of code it can be invoke by using Exec.

 so if i have any sql query that  i write over  and over again then i will save it to store procedure and just call it to execute it 

HOW TO CREATE STORE PROCEDURE:

first select your database then click on Programmability and in  Programmability we have Store Procedure.

 so for creating a new Store Procedure just right click on Store procedure and select New Store Procedure

SO here patientEFcore  is my database and inside it i have Programmability and in  Programmability i have Store Procedure.

so in PatientEFcore i have tables

here in tblPatient i am creating Store Procedure for Patientid . 

soo this is my  new Store Procedure 

CREATE PROCEDURE Sp_getpatientId       //Sp_getpatientId i named my store Procedure

@Id int                                                     // it is for input parameter

AS

BEGIN                                                             // inside begin we write our query

select * from tblPatient where patientid=@Id

END

GO 

    

and execute it .So after execution it will successfully create our store procedure with name Sp_getpatientId 

store procedure will invoke by using   EXEC procedure_name; so in new query we will write 

exec  Sp_getpatientId 2                                                             // here 2 is our PatientId

 

FUNCTIONS:-  

function can bring scaled value(single value)and it can only do computation(count(),min(),max(),sum(),avg())

 function can increase reusability in inside select 

HOW TO CREAT FUNCTION:-

first select your database then click on Programmability and in  Programmability we have  Function

 so for creating a new Function just right click on Function and select Scaled-valued Function                  [here we r doing scaled function]

this is  scaled-view function 

In scaled-view function we write a query for find name of max Rollno

Code for function :-

CREATE FUNCTION FN_maxRno                                                            //function name   1

(

)

RETURNS varchar(20)                                                                                 // datatype of name varchar 2

AS

BEGIN

Declare @name varchar(20)                                                  // here we will declare name with DT 3

select top 1 @name=name  from myrecord group by name ,rollno

order by rollno desc                                                                   // code ..

-- Return the result of the function

RETURN @name                                                                                  //return @name   4

END

GO                           

This is how look like   :-        

                                          

                                         

 

 Function can be called inside Select 

         SYNTAX:- SELECT  FUNCTIONNAME()

code for call function :-

           select [dbo].[FN_maxRno]() 

DIFFERENT BETWEEN STORE PROCEDURE AND FUNCTION:-

STORE PROCEDURE:-

1)Store Procedure :-Store Procedure is a mini program In store procedure we can do insert, update, delete , select ,inner join etc.

2) Store Procedure can return single and multiple value

3)Store Procedure can invoke by using exec

FUNCTION -

1)Function is a reusability function In function we cannot do insert , update ,select ,delete but in function we can do aggregations(Min(),Max() )

2)Function can return only single value

3)Function can call inside select