This topic describes how to create a stored procedure by using SQL Server.
Create one table :-
CREATE TABLE tbl_Students
(
[Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) NOT NULL,
[Lastname] [nvarchar](200) NULL,
[Email] [nvarchar](100) NULL
)
Support we insert the following data into the above table:-
Insert into tbl_Students (Firstname, lastname, Email)
Values('Vivek', 'Johari', 'vivek@abc.com')
Insert into tbl_Students (Firstname, lastname, Email)
Values('Pankaj', 'Kumar', 'pankaj@abc.com')
Insert into tbl_Students (Firstname, lastname, Email)
Values('Amit', 'Singh', 'amit@abc.com')
Insert into tbl_Students (Firstname, lastname, Email)
Values('Manish', 'Kumar', 'manish@abc.comm')
Insert into tbl_Students (Firstname, lastname, Email)
Values('Abhishek', 'Singh', 'abhishek@abc.com')
Now, while writing a Stored Procedure, the first step will be to write the Create Procedure statement as the first statement,
Create PROCEDURE Getstudentname( @studentid INT --Input parameter , Studentid of the student ) AS BEGIN SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid END
Note: It is not necessary that a stored procedure will
have to written. It can be the case when a stored procedure doesn't
written any thing. For Example, a stored procedure can be used to Insert, delete or update a sql statement. For Example the below stored procedure is used tp insert value into the table tbl_students.
/* This Stored procedure is used to Insert value into the table tbl_students. */ Create Procedure InsertStudentrecord ( @StudentFirstName Varchar(200), @StudentLastName Varchar(200), @StudentEmail Varchar(50) ) As Begin Insert into tbl_Students (Firstname, lastname, Email) Values(@StudentFirstName, @StudentLastName,@StudentEmail) End
