What is a Stored Procedure?
A stored procedure is a named group of SQL statements
that have been previously created and stored in the server database. Stored
procedures accept input parameters so that a single procedure can be used over
the network by several clients using different input data. And when the
procedure is modified, all clients automatically get the new version. Stored
procedures reduce network traffic and improve performance. Stored procedures
can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
Benefits of Using the Stored
Procedure
1.
Stored
procedure can reduce network traffic.
An operation requiring hundreds
of lines of Transact-SQL code can be performed through a single statement that
executes the code in a procedure, rather than by sending hundreds of lines of
code over the network.
2.
Stored
procedure allows faster execution.
If the operation requires a
large amount of SQL code is performed repetitively, stored procedures can be
faster. They are parsed and optimized when they are first executed, and a
compiled version of the stored procedure remains in memory cache for later use.
This means the stored procedure does not need to be reparsed and reoptimized
with each use resulting in much faster execution times.
3.
Helps
in re usability of the SQL code
It helps in re usability of
the SQL code because it can be used by multiple users and by multiple
clients since we need to just call the stored procedure instead of writing the
same SQL statement every time. It helps in reducing the development time.
4.
Stored
procedures provide better security to your data
Users can be granted permission
to execute a stored procedure even if they do not have permission to execute
the procedure's statements directly.
How to Write a Stored Procedure in SQL Server
Suppose there is a table called
tbl_Students whose
structure is given below: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 Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement used in the stored procedure
End
Now, suppose we need to create a Stored Procedure which
will return a
student name whose studentid is
given as the input parameter to the stored procedure. Then, the Stored
Procedure will be:/* Getstudentname is the name of the stored procedure*/
Create PROCEDURE Getstudentname(
@studentid INT --Input parameter , Studentid of the student
)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END
We can also collect the
student name
in the output parameter of the Stored Procedure. For example:/*
GetstudentnameInOutputVariable is the name of the stored procedure which
uses output variable @Studentname to collect the student name returns by the
stored procedure
*/
Create PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR(200) OUT -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END
Note:-/* */ is
used to write comments in one or multiple lines
-- is
used to write a comment in a single line
How to Alter a Stored Procedure in a SQL
Server
In SQL Server, a stored procedure can be modified with
the help of the
Alter keyword.
Now if we want to getstudent email
address through the same procedure GetstudentnameInOutputVariable.
So we need to modify it by adding one more output parameter "@StudentEmail "
which is shown below:/*
Stored Procedure GetstudentnameInOutputVariable is modified to collect the
email address of the student with the help of the Alert Keyword
*/
Alter PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR (200) OUT, -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname,
@StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END
Note: It is not necessary that a stored procedure
will have to return. There can be a case when a stored procedure doesn't
returns anything. For example, a stored procedure can be used to
Insert, delete orupdate a SQL
statement. For example, the below stored procedure is used to insert value into
the tabletbl_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
Execution of the Stored Procedure in
SQL Server
Execution of
the Stored Procedure which doesn't have an Output Parameter
A stored procedure is used in the SQL Server with the
help of the "
Execute"
or "Exec"
Keyword. For example, if we want to execute the stored procedure "Getstudentname",
then we will use the following statement.Execute Getstudentname 1
Exec Getstudentname 1
Execution of
the Stored Procedure using the Output Parameter
If we want to execute the Stored procedure "
GetstudentnameInOutputVariable"
, then we first need to declare the variable to collect the output values. For
example:Declare @Studentname as nvarchar(200) -- Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50) -- Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail -- "Select" Statement is used to show the output from Procedure
SQL Stored Procedure
Interview Questions with Answers
1.
What
are the Different Types of Stored procedures?
There are basically two types of stored procedures:
1.
System stored procedures
2. User defined stored procedures.
2.
What
is User defined stored procedures?
A user-defined procedure is stored procedures created by
user on user-defined database or in all system databases except the Resource
database.
3.
What
is System stored procedures?
System stored procedures are sql server inbuilt stored
procedures. All system stored procedures start with prefix _sp.
4.
What
is Temporary Stored procedure?
Temporary stored procedures are user defined stored
procedures stored in tempdb. Two types of temporary stored procedure Global and
local.
5.
Extended
User-Defined?
Extended procedures enable creating external routines in
a programming language such as C. These procedures are DLLs that an instance of
SQL Server can dynamically load and run.
7.
Can a
Stored Procedure call itself or a Recursive Stored Procedure? How many levels
of SP nesting are possible?
Yes. Because Transact-SQL supports recursion, you can
write stored procedures that call themselves.
You can nest stored procedures and managed code
references up to 32 levels.
8.
How
to Optimize a Stored Procedure using the Execution Plan?
1. Find the most costly statements
2. Determine why the statement
is costly
3. Get an accurate baseline for
the procedure
4. Optimize
9.
How
to Recompile Stored Procedure at Run Time?
When you create a stored procedure using the WITH
RECOMPILE option, it gets a brand new execution plan every time it runs.
This can be good for high performance queries because they get a plan perfect
for the variables that are passed in.
Example:
CREATE PROCEDURE dbo.GetProducts (@CatId INT)
WITH RECOMPILE
AS
SELECT *
FROM dbo.Products
WHERE Catid >=@CatId AND DeletedDate is null
10. Can we crate USP(User defined Stored
Procedure) on system databases also?
Yes, except "Resource" database.
11.
Can we
create a USP with same name as already exist in Master DB?
Yes
12.
If you
have created a USP with same name as exist in system DB Master then which one
will get executed first?
Master DB’s SP only executed
13.
How many
types of USP can be created?
1. USP
2. Temporary (same as USP except stored in
"tempdb") - On the
basis of name, visibility and availability, Temporary USP, further classified
as –
I.
Local (#, to current user
connection, auto deleted when connection is closed)
II.
Global (##, to any user after creation,
auto deleted when the last session using proc ends)
14.
Recursive
procedure:
declare @ret_val int
exec calc_factorial_value 3, @ret_val output
select @ret_val as result
create procedure calc_factorial_value ( @number int, @ret_val int out )
AS
declare @in int
declare @out int
if (@number != 1) -- @number = 4
BEGIN
select @in = @number - 1 -- @in = 3
exec calc_factorial_value @in, @out out -- 3, @out
select @ret_val = @number * @out
END
ELSE
BEGIN
select @ret_val = 1
END
RETURN
GO
15.
How to execute stored procedure as another user permission?
EXECUTE
AS user = 'special_user'
EXECUTE
YourProcerdureName
REVERT
No comments:
Post a Comment