Stored Procedures:- A stored Procedures is a group of a T-SQL statement. If you have a situation, where you write the same query over and over again, You can save that specific query as a Stored Procedure and call it just by its name.
Syntax:- Create procedure procedures name
As begin
SQL statement
End
Execute a Stored Procedures:-
exec procedure name
Stored Procedures Example:-
The following SQL statement creates a stored procedure named "SelectAllEmployee" that selects all records from the "tblemployee" table
Create proc SelectAllEmployee
as
Begin
Select * from tblemployee
End
Emp_Name Emp_ID Address
Lalita 2 Noida
Ankit 1 Noida
Ankita 3 Delhi
Anku 4 US
XYZ 5 UK
Stored Procedure With Multiple Parameters:-
Multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.
The following SQL statement creates a stored procedure that selects Tblemployee from a particular address with a particular PostalCode from the "tblemployee" table:
Example
CREATE PROC SelectAllEmployee @address nvarchar(30), @Emp_Id nvarchar(10)
AS
SELECT * FROM tblemployeeWHERE address = @address AND Emp_Id = @Emp_Id
GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllEmployee @address = 'London', @Emp_Id = '01';
To view the stored procedure in text:
sp_helptext stored_proc name
To change the stored procedure use,
alter procedure proc_name
as
begin
SQL statement
end
Drop Procedure
Drop procedure proc_name
To Encrypt the text of the Store Procedure
CREATE PROC SelectAllEmployee @address nvarchar(30), @Emp_Id nvarchar(10)
with encryption
AS
begin
SELECT * FROM tblemployeeWHERE address = @address AND Emp_Id = @Emp_Id
GO;