A stored procedure is used to group one or more T-SQL statements into a logical unit. It is stored inside the database server as an object. Each stored procedure in SQL always has a name, parameter lists, and T-SQL statements.

Let's create sample tables and insert some data to understand it with an example
  
CREATE TABLE TblStudent
(
    SRNo VARCHAR(15) NOT NULL PRIMARY KEY,
    StudentName VARCHAR(50),
    FatherName VARCHAR(50),
	Gender VARCHAR(10),
    CourseCode VARCHAR(15),
    PaidFee INT
)

GO

INSERT INTO TblStudent(SRNo, StudentName, FatherName,Gender, CourseCode,PaidFee) 
VALUES ('SR01','Kamal','Harish','Male','BCA',50000),
('SR02','Sachin','Gagan','Male','BBA',60000),
('SR03','Kapil','Raj','Male','MCA',0),
('SR04','Tanuj','Sanjeev','Male','BPharma',0),
('SR05','Puja','Shyam','Female','B.Arch',0)


GO

CREATE TABLE TblCourse
(
    CourseCode VARCHAR(15) NOT NULL PRIMARY KEY,
    CourseName VARCHAR(50),
    CourseFee INT
)

GO

INSERT INTO TblCourse(CourseCode,CourseName,CourseFee) VALUES
('BCA','Bachelor of Computer Applications',50000),
('BBA','Bachelors of Business Administration',60000),
('MCA','Master of Computer Applications' ,85000),
('BPharma','Master of Computer Applications' ,90000),
('B.Arch','Bachelor of Architecture' ,75000),
Creating a simple stored procedure without any parameters: We use CREATE PROCEDURE or CREATE PROC statement to create a stored procedure. Following stored procedure will fetch the student and course details by joining TblStudent and TblCourse tables. It does not have any input and output parameters.

CREATE PROCEDURE GetStudentDetails
AS
BEGIN
	SELECT S.StudentName,S.FatherName,C.CourseName,C.CourseFee,S.PaidFee
	FROM TblStudent S JOIN TblCourse C
	ON S.CourseCode=C.CourseCode
END
You can use the given methods to execute a stored procedure
1. Type and select the stored procedure name and then press F5 key to execute it.
2. EXEC GetStudentDetails
3. EXECUTE GetStudentDetails
4. Right-click on the procedure name, in object explorer in SQL Server Management Studio and select EXECUTE STORED PROCEDURE

When we execute the procedure GetStudentDetails, the result set looks like below.
Note: The stored procedures created by us(User) are called User defined stored procedures and there are some stored procedures which automatically created when we install SQL server. These stored procedures are called system stored procedures and start with 'sp_'. These are used by SQL Server internally.

We should not prefix user defined stored procedure name with 'sp_' because all system defined stored procedures are prefixed with 'sp_'.This avoids any ambiguity and conflicts between user defined and system stored procedures.

Creating a stored procedure with input parameters: The following stored procedure accepts the @CourseCode and @Gender as input parameters and produces a result set based on these parameters.
           
CREATE PROCEDURE GetStudentDetailsByParameter
(
	@CourseCode VARCHAR(15),
	@Gender VARCHAR(10)
)
AS
BEGIN
	SELECT S.StudentName,S.FatherName,S.Gender,C.CourseName,C.CourseFee,S.PaidFee
	FROM TblStudent S JOIN TblCourse C
	ON S.CourseCode=C.CourseCode
	WHERE S.CourseCode=@CourseCode AND S.Gender=@Gender
END
To execute this procedure, we need to provide input values for @CourseCode and @Gender parameters. If you don't want to specify the name of the parameters in EXECUTE command then you have to provide values to parameters in the same order as they are defined in the procedure. So first you have to provide value for the @CourseCode parameter and then for @Gender as given below

EXEC GetStudentDetailsByParameter 'BBA','Male'

Output:
In EXECUTE Command when you provide value to a parameter with it's name, then parameter order doesn't matter as given below. In Create Stored Procedure first we have defined @CourseCode and then @Gender, but we change parameter order in EXECUTE command.

EXEC GetStudentDetailsByParameter @Gender='Male',@CourseCode='BBA'

You can use the given methods to view the text of the stored procedure
1. Use system stored procedure sp_helptext 'SPName'
OR
2. Right Click the SP in Object explorer -> Scrip Procedure as -> Create To -> New Query Editor Window

Creating an encrypted stored procedure: You can use WITH ENCRYPTION option to encrypt the stored procedure text as given below.
         
CREATE PROCEDURE GetStudentDetailsByParameter
(
	@CourseCode VARCHAR(15),
	@Gender VARCHAR(10)
)
WITH ENCRYPTION
AS
BEGIN
	SELECT S.StudentName,S.FatherName,S.Gender,C.CourseName,C.CourseFee,S.PaidFee
	FROM TblStudent S JOIN TblCourse C
	ON S.CourseCode=C.CourseCode
	WHERE S.CourseCode=@CourseCode AND S.Gender=@Gender
END
You can not view the text of the stored procedure by using sp_helptext after encrypt it by 'WITH ENCRYPTION' option. You will get the below error if try to view the text of the procedure using sp_helptext.



You will get the below error when trying to view code using SQL Server management studio

Modifying the stored procedure: You can use ALTER PROCEDURE statement to do any modification or changes in the existing stored procedure. Earlier we have created a simple stored procedure 'GetStudentDetails' without any parameter. Now you can use ALTER PROCEDURE to adding a @CourseCode parameter as given below
         
ALTER PROCEDURE GetStudentDetails
(
@CourseCode VARCHAR(15)
)
AS
BEGIN
	SELECT S.StudentName,S.FatherName,C.CourseName,C.CourseFee,S.PaidFee
	FROM TblStudent S JOIN TblCourse C
	ON S.CourseCode=C.CourseCode
	WHERE S.CourseCode=@CourseCode
END
Renaming the stored procedure
You can use a system stored procedure sp_rename to rename a stored procedure using T-SQL. In the following example, we rename the procedure “GetStudentDetails” to a new name “GetStudentDetails_new”.