Stored Procedure
A stored procedure is a group of sql statements that has been created and stored in the database. If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by it's name. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure
Benefits of Using the Stored Procedure
1- One of the main benefits of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement) which is executing in a loop to the server through network and the network gets disconnected, then the execution of the SQL statement doesn't return the expected results, if the SQL query is not used between Transaction statement and rollback statement is not used.
2- Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time.
3- 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 procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.
5- Sometimes, it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.
Disadvantage of using Stored Procedures
1-Increase in server processing requirement: Using stored procedures can increase the amount of server processing. In a large user environment with considerable activity in the server, it may be more desirable to offload some of the processing to the client side.2-Business Logic in SP: Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.
Syntax:
Create Procedure Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement or query used in the stored procedure
End
Types of Stored Procedure
1-System Defined Stored Procedure
2- Extended Procedure
3- User Defined Stored Procedure
4- CLR Stored Procedure
1-System Defined Stored Procedure
These stored procedure are already defined in Sql Server. These are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database. These procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures. Here is a list of some useful system defined procedure.
System Procedure
sp_rename: It is used to rename an database object like stored procedure,views,table etc.
sp_changeowner: It is used to change the owner of an database object.
sp_help: It provides details on any database object.
sp_helpdb: It provide the details of the databases defined in the Sql Server.
sp_helptext: It provides the text of a stored procedure reside in Sql Server
sp_depends: It provide the details of all database objects that depends on the specific database object.
2- Extended Procedure
Extended procedures provide an interface to external programs for various maintenance activities. These extended procedures starts with the xp_ prefix and stored in Master database. Basically these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.
Example Below statements are used to log an event in the NT event log of the server without raising any error on the client application.
declare @logmsg varchar(100)
set @logmsg = suser_sname() + ': Tried to access the dotnet system.'
exec xp_logevent 50005, @logmsg
print @logmsg
Example The below procedure will display details about the BUILTIN\Administrators Windows group.
EXEC xp_logininfo 'BUILTIN\Administrators'
3-User Defined Stored Procedure
These procedures are created by user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.
4-CLR Stored Procedure
CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#. I will discuss CLR stored procedure later.
Note:
1-We can nest stored procedures and managed code references in Sql Server up to 32 levels only. This is also applicable for function, trigger and view.
2-The current nesting level of a stored procedures execution is stored in the @@NESTLEVEL function.
3-In Sql Server stored procedure nesting limit is up to 32 levels, but there is no limit on the number of stored procedures that can be invoked with in a stored procedure
How to create a Stored Procedure in SQL Server
Step-1 Create a table
CREATE TABLE Employee(
EmpID int Identity(1,1) NOT NULL,
EmpName nvarchar(50) NULL,
EmpGender nvarchar(50) NULL,
EmpSalary int NULL,
)
Step-2 create a procedure like below
Step-3 Now we have already inserted data into Employee table.
Step-4 Here we will get employee salary from Employee table using store procedure passing a parameter EmpID so create a stored procedure.
create Procedure getEmployeeSalary
(
@EmpID varchar(10)
)
as
begin
SELECT EmpSalary FROM Employee where EmpID=@EmpID
end
Step-5 Here we will execute stored procedure and pass a parameter like below.
or we can write the syntax of execute a procedure
Exec getEmployeeSalary 1
Execute getEmployeeSalary 1
output will be same like above image.
How to Alter a Stored Procedure in a SQL Server
create Procedure getEmployeeSalary
(
@EmpID varchar(10)
)
as
begin
SELECT EmpSalary FROM Employee where EmpID=@EmpID
end
Drop or Delete a Stored Procedure
how to drop a stored procedure. As you can see below to delete a stored procedure DROP keyword is used proceeded by the name of the stored procedure.
Drop Procedure Procedure-Name
A stored procedure is a group of sql statements that has been created and stored in the database. If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and call it just by it's name. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure
Benefits of Using the Stored Procedure
1- One of the main benefits of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement) which is executing in a loop to the server through network and the network gets disconnected, then the execution of the SQL statement doesn't return the expected results, if the SQL query is not used between Transaction statement and rollback statement is not used.
2- Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time.
3- 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 procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.
5- Sometimes, it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.
Disadvantage of using Stored Procedures
1-Increase in server processing requirement: Using stored procedures can increase the amount of server processing. In a large user environment with considerable activity in the server, it may be more desirable to offload some of the processing to the client side.2-Business Logic in SP: Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.
Syntax:
Create Procedure Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement or query used in the stored procedure
End
Types of Stored Procedure
1-System Defined Stored Procedure
2- Extended Procedure
3- User Defined Stored Procedure
4- CLR Stored Procedure
1-System Defined Stored Procedure
These stored procedure are already defined in Sql Server. These are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database. These procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures. Here is a list of some useful system defined procedure.
System Procedure
sp_rename: It is used to rename an database object like stored procedure,views,table etc.
sp_changeowner: It is used to change the owner of an database object.
sp_help: It provides details on any database object.
sp_helpdb: It provide the details of the databases defined in the Sql Server.
sp_helptext: It provides the text of a stored procedure reside in Sql Server
sp_depends: It provide the details of all database objects that depends on the specific database object.
2- Extended Procedure
Extended procedures provide an interface to external programs for various maintenance activities. These extended procedures starts with the xp_ prefix and stored in Master database. Basically these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.
Example Below statements are used to log an event in the NT event log of the server without raising any error on the client application.
declare @logmsg varchar(100)
set @logmsg = suser_sname() + ': Tried to access the dotnet system.'
exec xp_logevent 50005, @logmsg
print @logmsg
Example The below procedure will display details about the BUILTIN\Administrators Windows group.
EXEC xp_logininfo 'BUILTIN\Administrators'
3-User Defined Stored Procedure
These procedures are created by user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.
4-CLR Stored Procedure
CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#. I will discuss CLR stored procedure later.
Note:
1-We can nest stored procedures and managed code references in Sql Server up to 32 levels only. This is also applicable for function, trigger and view.
2-The current nesting level of a stored procedures execution is stored in the @@NESTLEVEL function.
3-In Sql Server stored procedure nesting limit is up to 32 levels, but there is no limit on the number of stored procedures that can be invoked with in a stored procedure
How to create a Stored Procedure in SQL Server
Step-1 Create a table
CREATE TABLE Employee(
EmpID int Identity(1,1) NOT NULL,
EmpName nvarchar(50) NULL,
EmpGender nvarchar(50) NULL,
EmpSalary int NULL,
)
Step-2 create a procedure like below
Step-3 Now we have already inserted data into Employee table.
Step-4 Here we will get employee salary from Employee table using store procedure passing a parameter EmpID so create a stored procedure.
create Procedure getEmployeeSalary
(
@EmpID varchar(10)
)
as
begin
SELECT EmpSalary FROM Employee where EmpID=@EmpID
end
Step-5 Here we will execute stored procedure and pass a parameter like below.
or we can write the syntax of execute a procedure
Exec getEmployeeSalary 1
Execute getEmployeeSalary 1
output will be same like above image.
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.
create Procedure getEmployeeSalary
(
@EmpID varchar(10)
)
as
begin
SELECT EmpSalary FROM Employee where EmpID=@EmpID
end
Drop or Delete a Stored Procedure
how to drop a stored procedure. As you can see below to delete a stored procedure DROP keyword is used proceeded by the name of the stored procedure.
Drop Procedure Procedure-Name
No comments :
Post a Comment