A stored procedure in SQL Server is a group of one
or more Transact-SQL statements that has been created and
stored in the database. Stored procedure will accept input parameters.
Why we need to use Stored Procedure
Here I am taking a simple
example. I have an application which is install in more than 1000 machine.
Application has some functionality like Registration, sells, inventory and
reports etc. For each functionality application interact with the database server.
Just think if we are not creating database object which store the sql statement
like select, insert update and delete statement then what happen. We need to
write these statements in application code itself.
For Each request they send the set
of T-Sql statement.
There are following disadvantage when we keep the code on client machine
Ø
Network
traffic is high – suppose sql statement is having 1000 line of code then
each request we need to send number of bits through network to connect the database
server and get the response from server. This is for one client machine then
thinks about no of client machine.
Ø
Maintenance
of the code is difficult – we are keeping the code at client side then
think if we need to do some changes on the sql statement we need to update this
code on every machine. Again we need to install this on every machine. It is
very tedious task.
This code deploy on the every client machine. The
above sql statement will return top 10 records. But now suppose our requirement
will be changed and now we want to display the top 15 records then again we
need to changes the sql statement
And we need to ageing deploy it in all machines. It is
very tedious task.
Ø
No
Security for the code I means it will be easily hacked – Code at the client
machine. It can view and easily hacked. Then can change the code because they
know the structure the table. In place of select they can write the insert or
update statement.
So there is no security.
Ø
Performance
of the code is slow: For each sql statement every time sql server will
create the execution plan for executing the sql script. So it will be the slow.
To over coming this issue we need to keep the T-sql script
on the central location or we need to store it on the database server itself.
To keep the T-Sql
script at the server side we have following option
Ø
View
Ø
Stored Procedure
Ø
Function
View and function we will see the next post.
Stored Procedure
A
stored procedure is nothing more than prepared SQL code that we save in the
database server as an object so we can reuse the code over and over
again. Just call the stored procedure to execute the SQL code that you
saved as part of the stored procedure.
There are two type of stored procedure
Ø
User defined
Stored Procedure
Ø
System Stored procedure
User Define Stored Procedure
A Stored Procedure (SP) which is created by the developer is
knows as User define Stored Procedure
Syntax
See the example
CREATE PROCEDURE
Usp_GetEmpDetails
(
--Input
parameters
@EmpId varchar(10),
--Output
parameters
@Name varchar(100) OUT
)
AS
BEGIN
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM
[AdventureWorksDW2008R2].[dbo].[DimEmployee]
where
EmployeeKey =@EmpId
select @Name= (select FirstName FROM
[AdventureWorksDW2008R2].[dbo].[DimEmployee]
where EmployeeKey =@EmpId)
Print @Name
END
GO
|
Now Press F5 to create the SP.
We see this sp on below
For executing the Sp
EXEC or Execute
Now see
the Output parameter
We can execute it by GUI
Right Click on SP name and select Execute Stored Procedure.
We will get the new window in that we need to supply the Input parameter.
Click ok
If you want to make the changes on the SP we need to User ALTER
Execute it or Press F5 the changes will be reflecting.
Suppose if you want to see the content of SP in future then
there is two way to see
1.
Using GUI
Right click on the SP
And Click on the Modify.
We will get the SP content
2.
2nd
way to get the content of the SP is using System Stored procedure
SP_helptext <Sp
name>
SP_helptext 'Usp_GetEmpDetails'
|
Creating SP with default parameter
When we declaring the Input parameter in SP and if we assign
the values of that input parameter that is treated as default parameter. See
below
CREATE PROCEDURE
[dbo].[Usp_GetEmpDetails_DefaultPara]
(
@EmpId varchar(10)=1
)
AS
BEGIN
SELECT [EmployeeKey]
,[FirstName]
,[LastName]
,[MiddleName]
,[HireDate]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[StartDate]
,[EndDate]
,[Status]
FROM
[AdventureWorksDW2008R2].[dbo].[DimEmployee]
where
EmployeeKey =@EmpId
END
|
Now Executing this SP
Exec Usp_GetEmpDetails_DefaultPara 1
EXEC Usp_GetEmpDetails_DefaultPara
--by default it will take the Input parameter value is 1
|
When we execute the SP if we are not supplying the value it
will take the default value.
Using Return in Stored
procedure
If the Stored procedure is executed successfully then it
will return the 0 (zero) else it returns non-zero.
See the example
Create PROCEDURE
[dbo].[Usp_GetEmpDetails_Return]
(
@EmpId varchar(10)=1,
@Result varchar(100) OUT
)
AS
BEGIN
declare @Rec_count int;
select @Rec_count=Count(*) FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
where
EmployeeKey =@EmpId
IF
@Rec_count=0
Begin
Print 'Employee doesn''t Exist'
return 10
End
Else
Begin
SELECT [EmployeeKey],[FirstName],[LastName],[MiddleName],[HireDate],[BirthDate],
[MaritalStatus],[Gender],[StartDate],[EndDate],[Status]
FROM
[AdventureWorksDW2008R2].[dbo].[DimEmployee]
where
EmployeeKey =@EmpId
End
END
|
Here I am taking the simple example in this SP I am passing
the EmpID and checking that EmpID is exist or not. If the EmpID is not exist
the returning 10 else displaying the EmpID details
Now I am executing the SP
I am supplying the EmpID=10. See the result below.
Now I am going to supply EmpID which doesn’t exist on the
DimEmployee table
See the Message and also see the Return value.
Recursive Stored procedure
A stored procedure can call itself. If a Stored procedure
calls itself then we called this SP is recursive stored procedure. We can call
up to 32 nested level of the SP.
Here I am taking simple example. In this example I am
creating a factorial
CREATE PROCEDURE
Factorial
(
@Number Integer,
@Return Varchar(100) OUTPUT
)
AS
DECLARE
@In Integer
DECLARE
@Out Integer
IF
@Number != 1
BEGIN
Set
@In = @Number -
1
EXEC
Factorial @In,
@Out OUTPUT
SET
@Return = @Number *
@Out
END
ELSE
BEGIN
SELECT
@Return = 1
END
RETURN @Return
GO
|
Now I am executing this SP
declare @x int
Exec @x= Factorial 5,@x OUTPUT
Print 'Factorial Value : ' + Cast(@x as varchar(20))
|
Benefit of Stored procedure
Ø
Execution
plan reused
Ø
Reduce
the Network traffic
Sp reduce the network traffic because see
the below Stored procedures are stored in the database server. Client sends the
SP name as well as parameter only.
Ø
Better
security
We can provide the security to the Sp
Ø
Reusable
Ø
Avoid the
SQL injection attack
Some points regarding SP
Ø
Sp can return Zero, single or multiple values.
Ø
We can use Transaction
Ø
We can use Try Catch block (Error Handling)
Ø
We can call Function or SP
Ø
We can use DML,DDL,TCl command
Ø
We can’t use SP as a select or Where ( select
Exec Factorial 5)
Ø
SP can have up to 21,000 input parameter
Ø
Don’t start the SP name with SP_ Prefix because
if System SP having the same prefix. If we use this prefix then it will be slow
because when we execute this SP first it will go to the system Stored Procedure
and search there. So avoid this prefix.
System Stored Procedure
System Stored
Procedures are useful in performing administrative and informational activities
in SQL Server. Most commonly used system Stored Procedures are described below.
We can find it on the below location
List
of some Important Stored Procedure
Ø
SP_Help : it will
give the information of each database object(table, view, function etc) which
are stored in the current Database
Ø
SP_Helpdb
: it will give the information of all
database which are in the database server.
Ø
SP_HelpText
: it will return the text of the Database object (View and SP)
Ø
SP_Spaceused
: it will return the size of current database
Ø Sp_who : Provides
information about current users, sessions, and processes in an instance of the
Microsoft SQL Server Database Engine. The information can be filtered to return
only those processes that are not idle, that belong to a specific user, or that
belong to a specific session
Ø SP_table : Return the list of database object
which are stored in the current database.
Ø SP_Columns <table_name> :
Example
: SP_Columns ‘FactFinance’
It returns
the list of the column name with all details of the supplied table.
