Stored procedure:
------------------
Stored procedures are special objects available in sql server. Its a precompiled statements where all
the preliminary parsing operations are performed and the statements are ready for execution.
Its very fast when compared to ordinary sql statements where the sql statements will undergone a sequence of steps to
fetch the data
Stored procedure involves various syntax based on the parameters passed.
Let me show you a generic syntax for a stored procedure.
create procedure proc_name
as
begin
sql statement1
sql statement2
end
ex:2
create procudere getempproc
as
begin
select * from employee
end
creating the parameterized stored procedures:
---------------------------------------------
we want to execute a procedure for a different values of a variable that are provided
at run time.for this we can create the parameterized stored procedures
the parameters are used to pass values to the stored procedure during run time.
USE test1
GO
IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE [TYPE] = 'P' AND NAME = 'GetMarks')
DROP PROCEDURE GetMarks
GO
CREATE PROCEDURE GetMarks
(
@m1 int=0,
@m2 int=0,
@m3 int=0,
@m4 int=0,
@m5 int=0,
@m6 int=0
)
AS
BEGIN
DECLARE @Tot INT,
DECLARE @Avg FLOAT,
SELECT @Tot = @m1+@m2+@m3+@m4+@m5+@m6,
@avg=@Tot/6
if(@m1>=35&& @m2>=35&& @m3>=35&& @m4>=35 && @m5>=35 && @m6>=35)
BEGIN
PRINT 'pass'
if(@avg>=90)
PRINT 'first division'
else
if(@avg>=75)
PRINT 'second division '
else
if(@avg>=35)
PRINT 'third division '
else
PRINT 'fail'
END
END
by ushing the stored procedure we can insert the elements into the table
ex:
TABLE1:
IF NOT EXISTS(SELECT * FROM SYS.OBJECTS
WHERE name = 'Student_Detail' AND [type] = 'U')
BEGIN
CREATE TABLE Student_Detail
(
StudentId INT IDENTITY(1,1)
CONSTRAINT [PK_Student_Detail_StudentId] PRIMARY KEY,
FirstName VARCHAR(75) NOT NULL,
LastName VARCHAR(25) NOT NULL,
DOB DATETIME NOT NULL,
)
END
GO
TABLE2:
IF NOT EXISTS(SELECT * FROM SYS.OBJECTS
WHERE name = 'Cource' AND [type] = 'U')
BEGIN
CREATE TABLE Cource
(
CourceId int IDENTITY(100,10)
CONSTRAINT [PK_Cource_CourceId] PRIMARY KEY,
Cource_Code VARCHAR(10) ,
CourceName VARCHAR(25) NOT NULL
)
END
TABLE3:
IF NOT EXISTS(SELECT * FROM SYS.OBJECTS
WHERE name = 'CITY' AND [type] = 'U')
BEGIN
CREATE TABLE CITY
(CityId INT IDENTITY(600,1)
CONSTRAINT [PK_CITY_CityId] PRIMARY KEY,
CityCode VARCHAR(10) ,
CityName VARCHAR(50))
END
TABLE4:
IF NOT EXISTS(SELECT * FROM SYS.OBJECTS
WHERE name = 'STUDENT_CITY' AND [type] = 'U')
BEGIN
CREATE TABLE STUDENT_CITY
(
StudentCityId INT IDENTITY(111,110)
CONSTRAINT PK_STUDENT_CITY_StudentCityId PRIMARY KEY,
StudentId INT
CONSTRAINT [FK_STUDENT_CITY_StudentId] FOREIGN KEY(StudentId) REFERENCES Student_Detail(StudentId),
CityId INT CONSTRAINT FK_STUDENT_CITY_CityId FOREIGN KEY(CityId)
REFERENCES CITY(CityId)
)
END
TABLE5:
IF NOT EXISTS(SELECT * FROM SYS.OBJECTS
WHERE name = 'STUDENT_COURCE' AND [type] = 'U')
BEGIN
CREATE TABLE STUDENT_COURCE
(
StudentCourceId INT IDENTITY(1,1)
CONSTRAINT PK_STUDENT_COURCE_StudentCourceId PRIMARY KEY,
StudentId INT CONSTRAINT [FK_STUDENT_COURCE_StudentId] FOREIGN KEY(StudentId) REFERENCES Student_Detail(StudentId),
CourceId INT CONSTRAINT [FK_STUDENT_COURCE_courceId] FOREIGN KEY (CourceId) REFERENCES Cource(CourceId)
)
END
HERE FIVE TABLES ARE THERE BY USHING THE STORED PROC WE WILL UPDATE THE ALL THE TABLES
Use VENKAT
GO
IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE [TYPE] = 'P' AND NAME = 'SetStudentDetails')
DROP PROCEDURE SetStudentDetails
CREATE PROCEDURE SetStudentDetails
(
@firstname VARCHAR(75),
@lastname VARCHAR(25),
@dob DATETIME,
@cource VARCHAR(25),
@city VARCHAR(50)
)
AS
/*******************************************************
CREATED BY :
CREATED DATE : 5//2012
DESC : procedure to SetStudentDetails
CHANGE HISTORY
NAME DATE DESC
USAGE:
EXEC SetStudentDetails 'NARI','A','1989-03-15','MBA','KHAMMAM'
********************************************************/
SET NOCOUNT ON
BEGIN
DECLARE @StudentIdentity INT
IF NOT EXISTS(SELECT * FROM Student_Detail WHERE FirstName = @firstname AND LastName = @lastname AND DOB = @dob)
INSERT INTO Student_Detail(
FirstName,
LastName,
DOB
)
VALUES
(
@firstname,
@lastname,
@dob
)
SELECT @StudentIdentity = @@IDENTITY
DECLARE @CourceIdentity INT, @CityIdentity INT
IF NOT EXISTS(SELECT * FROM Cource WHERE CourceName= @cource)
INSERT INTO Cource
(
Cource_Code,
CourceName
)
VALUES
(
Left (@cource,3),
@cource
)
SELECT @CourceIdentity = CourceId FROM Cource WHERE CourceName= @cource
IF NOT EXISTS(SELECT * FROM CITY WHERE CityName = @city)
INSERT INTO CITY
(
CityCode,
CityName
)
VALUES
(
Left(@city, 4),
@city
)
SELECT @CityIdentity = CityID FROM CITY WHERE CityName = @city
IF NOT EXISTS(SELECT * FROM student_cource WHERE StudentId =@StudentIdentity )
INSERT INTO student_cource
(
StudentId,
CourceId
)
vALUES
(
@StudentIdentity,
@CourceIdentity
)
DECLARE @StudentCourceId INT
SELECT @StudentCourceId = @@IDENTITY
IF NOT EXISTS(SELECT * FROM STUDENT_CITY WHERE StudentId=@StudentIdentity )
INSERT INTO STUDENT_CITY
(
StudentId,
CITYID
)
vALUES
(
@StudentIdentity,
@CITYIdentity
)
END
EX2:
USE VENKAT
GO
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE='P' AND NAME='GetStudentDetails')
DROP PROCEDURE GetStudentDetails
GO
CREATE PROCEDURE GetStudentDetails
(
@StudentID INT
)
AS
/*******************************************
Author :
Date : 05/01/2012
Desc : Procedure to get Student Details
Usage : EXEC GetStudentDetails 1
Change History :
Name Date Description
********************************************/
SET NOCOUNT ON
BEGIN
IF EXISTS(SELECT * FROM Student_Detail WHERE StudentID = @STUDENTID)
SELECT SD.StudentID,
SD.FirstName,
SD.LastName,
SD.DOB,
CR.CourceId,
CR.Cource_Code,
CR.CourceName,
C.CityID,
C.CityCode,
C.CityName
FROM Student_Detail SD
INNER JOIN Student_City SC
ON SC.StudentID = SD.StudentID
INNER JOIN City C
ON C.CityID = SC.CityID
INNER JOIN STUDENT_COURCE SCR
ON SCR.StudentID = SD.StudentID
INNER JOIN Cource CR
ON CR.CourceId = SCR.CourceId
WHERE SD.StudentID = @StudentID
ELSE
BEGIN
PRINT 'THIS ID IS NOT EXISTS...THERE IS NO STUDENT'
END
END
