Stored Procedure

If We have to Write Same Query Over and Over Again ,you can save the specific Query as Stored Proc.

1) Group of SQL Statement. (pre compiled SQL statement)
2) SP accept input parameter so that a single procedure can be used over the network by several clients
using different input data.
3) Reduce Network traffic
4) Reusability
5) Execution plan retension.
6) Improved Security From SQL Injection.
7) Compile at one time only
8) SP can have both input & output parameter

--------------------------------Create Procedure---------------------
Create Procedure RetrieveData
(@ID int)
As
Begin
Select id,AadharcardNo from EMPTest Where id=@ID 
end
--Imp-If Table1 is not present in Database still we can write Table1 in SP, but when we execute it gives error-Invalid object name 'Table1'.

--------Call-------------------

Exec RetrieveData 1

-----------------------------------------Alter Sp
Alter Procedure RetrieveData
(@ID int)
As
Begin
Select id,AadharcardNo from EMPTest Where id=@ID 
end

------------------------------------------------------------
Alter procedure Retrieve_Test
@TestID Bigint
as
Begin
if @TestID=0 
Set @TestID=NULL
select * from Test where TestId=COALESCE(@TestID,Test.TestID)
End
exec Retrieve_Test NULL

----------------------------------------Output or out parameter in SP
Alter Procedure RetrieveData
(
@ID int,
@Name Varchar(50) Output
)
As
Begin
Select @Name=Name from EMPTest Where id=@ID 
end

-----------------------------------------Call Sp
DECLARE @Name Varchar(50)
EXEC RetrieveData 1, @Name OUTPUT
SELECT @Name

-----------------------------------------Drop Sp
Drop Procedure RetrieveData

SQL Injection