/*
Stored Procedure(SP): is a set of
precompiled queries. Whenever you execute the normal query, it has to be
compiled first, and then only it will get executed. When you execute the SP
first time ,it will be compiled and executed at that time only and also it will
create some execution plan when you execute next time it won’t get compiled
unless you make any changes. It will get executed directly
and reuse the existing execution plan to execute the query. So it will be faster
as compare to normal sql query.
*/
Creating the EmployeeInfo table and populate the table with
some data.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmployeeInfo](
[EmpID]
[int] IDENTITY(1,1) NOT NULL,
[FirstName]
[nvarchar](50) NULL,
[LastName]
[nvarchar](50) NULL,
[Age]
[int] NULL,
[Gender]
[varchar](10) NULL,
CONSTRAINT
[PK_EmployeeInfo] PRIMARY KEY CLUSTERED
(
[EmpID]
ASC
)WITH (PAD_INDEX =
OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Inserting Records into the EmployeeInfo table as shown
below:-
INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName],
[Age], [Gender]) VALUES ( N'Pat', N'Thomson', 24, N'Male')
INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName],
[Age], [Gender]) VALUES ( N'Ricky', N'Ponting', 25, N'Male')
INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName],
[Age], [Gender]) VALUES ( N'Venus', N'Williams', 32, N'Female')
INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName],
[Age], [Gender]) VALUES ( N'Serena', N'Williams', 35, N'Female')
INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName],
[Age], [Gender]) VALUES ( N'Steve', N'Waugh', 44, N'Male')
INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName],
[Age], [Gender]) VALUES ( N'Shane', N'Watson', 26, N'Male')
INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName],
[Age], [Gender]) VALUES ( N'Maria', N'Sharapova', 33, N'Female')
Selecting records from dbo.EmployeeInfo table as shown
below:-
SELECT * FROM EmployeeInfo
Now i am retreiving something like this as shown below:-
SELECT FIRSTNAME,LastName,Age FROM EmployeeInfo
/*
Whenever u need the above
information u need to run this over and over again.Instead of doing like this
we can wrap the above query into the stored procedure then we can call by its
name as shown below.
SYNTAX TO CREATE SP:-
CREATE PROC OR PROCEDURE PROCEDURENAME (PARAMETERS(OPTIONAL
ONE))
AS
BEGIN
SET OF SQL
STATEMENTS...
END
*/
CREATE PROC uspGetEmployeeInfo
AS
BEGIN
SELECT
FIRSTNAME,LastName,Age FROM EmployeeInfo
END
Once you created this sp , you
can find that SP in programmability-->stored procedures folder in the object
explorer under your database context.
We can execute
the sp in different ways as shown below.
1.
uspGetEmployeeInfo
2.
EXEC uspGetEmployeeInfo
3.
EXECUTE uspGetEmployeeInfo
4.
We can execute from the object explorer also by pressing
F8 key.
To view the text of
stored proc:-
SP_HELPTEXT uspGetEmployeeInfo
After created if you want to make any changes in stored proc
you can use alter command as shown below.
ALTER PROC uspGetEmployeeInfo
AS
BEGIN
SELECT
FIRSTNAME,LastName,Age FROM EmployeeInfo WHERE Gender='MALE'
END
To encrypt the SP:-
ALTER PROC uspGetEmployeeInfo WITH ENCRYPTION
AS
BEGIN
SELECT
FIRSTNAME,LastName,Age FROM EmployeeInfo WHERE Gender='MALE'
END
Now try to view the
text of the sp:-
SP_HELPTEXT uspGetEmployeeInfo
MSG:- T he text for object 'uspGetEmployeeInfo' is
encrypted.
If you try to open through object browser then also it will
throw some error.
-------------------------------------------------------------------------------------------------------------------------------------
We can even create the stored procedure
with parameters as well .it will accept both the input parameters and output
parameters.
Creating the sp with input parameters is shown below.
CREATE PROC uspGetEmployeeInfoByGender
@Gender varchar(10)
AS
BEGIN
SELECT * FROM
EmployeeInfo WHERE Gender=@Gender
END
To execute the SP:-
uspGetEmployeeInfoByGender 'FEMALE'
-------------------------------------------------------------------------------------------------------------------------------------
Stored proc with
output parameters:-
Look at the
employeeinfo table.Here we have both the male and female employee.Now i am
going to write the sp to count the employee by gender by using output
parameter.
To create the sp with
output parameter:-
CREATE PROC uspGetEmployeeCountByGender(@Gender
varchar(10),@TotalEmployeeCount int output)
AS
BEGIN
SELECT @TotalEmployeeCount=
COUNT(EMPID) FROM EmployeeInfo WHERE Gender=@Gender
END
To execute the sp
with output parameters:-
DECLARE @TOTALCOUNT INT
EXEC uspGetEmployeeCountByGender 'MALE',@TOTALCOUNT OUTPUT
PRINT @TOTALCOUNT
Just remove the output keyword above and execute.
DECLARE @TOTALCOUNT INT
EXEC uspGetEmployeeCountByGender 'MALE',@TOTALCOUNT
PRINT @TOTALCOUNT
-------------------------------------------------------------------------------------------------------------------------------------
Stored procedure with
return value:-
Whenever we execute the sp, it
will return zero or non-zero value.
Zero - indicates "success”. Non-zero - indicates
failure of sp.
Execute the sp in object browser and check out the return
value what it returns.
To execute the sp in the object explorer follow the below
steps.
Just select the sp which u want to execute...right click-->
execute stored procedure-->specify input parameter if applicable and click
ok.
CREATE PROC uspGetEmployeeCount
AS
BEGIN
RETURN(SELECT COUNT(EMPID) FROM EmployeeInfo)
END
Now executing the SP as shown below:-
DECLARE @TOTALCOUNT INT
EXEC @TOTALCOUNT=uspGetEmployeeCount
PRINT @TOTALCOUNT
-------------------------------------------------------------------------------------------------------------------------------------
To check out what are all the table the particular sp is
using and what are all the sp's we are
using in the specific table:-
SYNTAX: SP_DEPENDS 'PROCNAME'
SP_DEPENDS uspGetEmployeeCount
SP_DEPENDS EmployeeInfo
-------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment