A stored procedure is a named group of SQL statements that have been previously created and stored in the server database and can be invoked by program or trigger or stored procedure itself. You can say, stored procedure is way to execute your business logic directly into database server.
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.
In stored procedure you can perform your some of generic tasks which are dependent to database table data. So rather to go multiple time on database to fetch data into your program and perform your business logic, stored procedure give some generic way of coding for your business logic and take data return or you can save your processed data into your database. By this Stored procedures reduce network traffic and improve performance.
e.g Suppose we have normal banking system & we want to update interest of loan of any specific user. For this, we need to fetch capital, rate of interest and duration and calculate interest and then we will go back to database and save data.
If we use stored procedure, then we need to write all operation within our stored procedure and we will call it through the program one time. For this particular case, we will fetch capital, rate of interest and duration within database(using stored procedure) and save data after processing. By this, we are only interrupting database server one time, which will in turn reduce server overhead.
What are the advantages of Stored Procedure?
1). By using stored procedure you can make your business logic independent of programming language.
2). When security is main concern use of stored procedure is vital. By doing your operation through the database you can log your all performed action.
3). If we are using stored procedure, then we do not have to access table directly which is one more way to secure the data and transaction.
4). Stored procedure increases performance of our application. When stored procedure created it compile and when we call it, it will never go to parser, instead it directly execute and fetch the record whereas normal SQL query fired on database server get parse every time so using stored procedure your can save parsing time.
5). If application is big or database server on remote system then by using stored procedure we can decrease the traffic between our database server and application server.
6). Since stored procedure is written in our database server and application call it separately then the degree of re-usability increase because despite to going in much detail we can call stored procedure to perform our action.
What are the disadvantages of Stored Procedure?
1). Sometime using stored procedure is bit risky. As it follows the principle of "define one use many time", by changing the stored procedure directly effect your data so it should always be use with very carefully.
2). Stored procedure are set of sql command form our logic, so sometime programmer need to debug the stored procedure. In mysql, it's very hard to debug the stored procedure.
3). Managing stored procedure is bit difficult as it does not have any object oriented paradigm like things.
e.g:
Let's take a simple example, which will simple select one string.
mysql > create procedure helloTest() Select 'hello';
Above sql statement will create a stored procedure in mysql system with name helloTest. To call this procedure execute below query:
mysql > CALL heloTest();
This will run select 'hello'.
Above stored procedure is quite simple, now we will take learn to create some more complex stored procedures. Before that let's take a quick overview of how to create a lil complex stored procedures.
1). Definition of the procedure: Create procedure syntax with the name
2). Definition of the parameters: Mysql stored procedure never return value directly. Either you need to specify output parameter in your stored procedure or you have to put select statement inside the procedure which will fill data in your resource. There are three type of parameter you can define. in parameter, out parameter and inout parameter.
2.1). IN parameter you can define inputs of the stored procedure,
2.2). OUT parameter you can specify the output parameter.
2.3). INOUT define shared parameter, it can be used either as input parameter or output parameter.
3). Third is the body of the procedure. Normally we write within the BEGIN and END tag.
e.g:
DELIMITER //
CREATE PROCEDURE sp_test (IN capital DOUBLE , IN rate INT , IN duration INT , OUT interest DOUBLE)
BEGIN
SET interest = (capital * rate * duration)/100;
INSERT INTO audit_table(interest) VALUES(interest);
END //
DELIMITER ;
(We have first changed delimiter as // to mark end of stored procedure, and then reverted it back to previous delimiter.)
This will create a stored procedure with name sp_test, it take capital , rate and duration as input and return the interest in output variable after calculating it, it also insert the interest into database.
Let's run it.
mysql > call test(500 , 1.3 , 3 , @out_interest);
mysql > select @out_interest;
@out_interest will give you the interest calculated by the stored procedure 'sp_test'. In above case 'select @out_interest;' will return '19.5'.
We can use input parameter in the query written within stored procedure. e.g:
DELIMITER //
CREATE PROCEDURE new_sp_test (IN order_id VARCHAR(255))
BEGIN
SELECT * FROM Order WHERE Order.order_id = order_id;
END
END //
DELIMITER ;
How can we declare a variable in stored procedure?
We can declare variables inside a stored procedure after BEGIN tag. In mysql we use DECLARE tag for declaring a variable. We have to specify the data type of the variable to declare the variable.
Scope of the variable in mysql is limited to END tag, that means after END tag variable will be lost.
e.g:
DECLARE i INT(3)
DECLARE j INT(9) DEFAULT 6;
We can initialize the variable after declaration, e.g:
DECLARE i INT(3);
SET i = 10;
In mysql we can take he value in the variable from the query also from the help of INTO keyword. e.g:
DECLARE orderId VARCHAR(23);
SELECT order.order_id INTO orderId FROM order;
Condition statement in Mysql programming will give us power to execute code on the basis of the some value. e.g:
DECLARE order_count INT(5) default 0;
SELECT count(*) INTO order_count FROM order;
IF order_count > 5 THEN
SELECT * FROM order;
ELSE
SELECT 'Less than 5 orders';
END IF;
Always remember, for every IF statement in Mysql we have to specify END IF statement. In mysql we can also use ELSEIF for the recursive statements. e.g:
DECLARE order_count INT(5) default 0;
SELECT count(*) INTO order_count FROM order;
IF order_count =0 THEN
SELECT 'No orders present';
ELSEIF order_count > 5 THEN
SELECT * FROM order;
ELSE
SELECT 'Less than 5 orders';
END IF;
We can also use switch case based conditional statement in mysql. e.g:
CASE
WHEN i > 2 THEN
SELECT 'greater then two';
WHEN i < 2 THEN
Select 'less then two';
ELSE
SELECT 'other case';
END CASE;
Every CASE must be closed with END CASE.
How to implement error handling in Stored Procedure?
When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful error message.
The TRY CATCH construct allows you to gracefully handle exceptions in SQL Server. To use the TRY CATCH construct, we first place a group of Transact-SQL statements that could cause an exception in a BEGIN TRY...END TRY block and then you use a BEGIN CATCH...END CATCH block immediately after the TRY block.
If the statements between the TRY block complete without an error, the statements between the CATCH block will not execute. However, if any statement inside the TRY block causes an exception, the control transfers to the statements in the CATCH block.
BEGIN TRY
-- statements that may cause exceptions
END TRY
BEGIN CATCH
-- statements that handle exception
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState
END CATCH
The CATCH block functions
@@ERROR Function in SQL Server
The @@ERROR system function is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection.
If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designated error code. To determine if a statement executes successfully, an IF statement is used to check the value of the function immediately after the target statement executes.
One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.
USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
BEGIN
PRINT 'Error Occurred'
END
RAISERROR in SQL Server
We can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application. The following information is given about the parameters.
USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
BEGIN
RAISERROR ('An error occured updating the NonFatal table',10,1)
END
-K Himaanshu Shuklaa..
Also Read:
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.
In stored procedure you can perform your some of generic tasks which are dependent to database table data. So rather to go multiple time on database to fetch data into your program and perform your business logic, stored procedure give some generic way of coding for your business logic and take data return or you can save your processed data into your database. By this Stored procedures reduce network traffic and improve performance.
e.g Suppose we have normal banking system & we want to update interest of loan of any specific user. For this, we need to fetch capital, rate of interest and duration and calculate interest and then we will go back to database and save data.
If we use stored procedure, then we need to write all operation within our stored procedure and we will call it through the program one time. For this particular case, we will fetch capital, rate of interest and duration within database(using stored procedure) and save data after processing. By this, we are only interrupting database server one time, which will in turn reduce server overhead.
What are the advantages of Stored Procedure?
1). By using stored procedure you can make your business logic independent of programming language.
2). When security is main concern use of stored procedure is vital. By doing your operation through the database you can log your all performed action.
3). If we are using stored procedure, then we do not have to access table directly which is one more way to secure the data and transaction.
4). Stored procedure increases performance of our application. When stored procedure created it compile and when we call it, it will never go to parser, instead it directly execute and fetch the record whereas normal SQL query fired on database server get parse every time so using stored procedure your can save parsing time.
5). If application is big or database server on remote system then by using stored procedure we can decrease the traffic between our database server and application server.
6). Since stored procedure is written in our database server and application call it separately then the degree of re-usability increase because despite to going in much detail we can call stored procedure to perform our action.
What are the disadvantages of Stored Procedure?
1). Sometime using stored procedure is bit risky. As it follows the principle of "define one use many time", by changing the stored procedure directly effect your data so it should always be use with very carefully.
2). Stored procedure are set of sql command form our logic, so sometime programmer need to debug the stored procedure. In mysql, it's very hard to debug the stored procedure.
3). Managing stored procedure is bit difficult as it does not have any object oriented paradigm like things.
e.g:
Let's take a simple example, which will simple select one string.
mysql > create procedure helloTest() Select 'hello';
Above sql statement will create a stored procedure in mysql system with name helloTest. To call this procedure execute below query:
mysql > CALL heloTest();
This will run select 'hello'.
Above stored procedure is quite simple, now we will take learn to create some more complex stored procedures. Before that let's take a quick overview of how to create a lil complex stored procedures.
1). Definition of the procedure: Create procedure syntax with the name
2). Definition of the parameters: Mysql stored procedure never return value directly. Either you need to specify output parameter in your stored procedure or you have to put select statement inside the procedure which will fill data in your resource. There are three type of parameter you can define. in parameter, out parameter and inout parameter.
2.1). IN parameter you can define inputs of the stored procedure,
2.2). OUT parameter you can specify the output parameter.
2.3). INOUT define shared parameter, it can be used either as input parameter or output parameter.
3). Third is the body of the procedure. Normally we write within the BEGIN and END tag.
e.g:
DELIMITER //
CREATE PROCEDURE sp_test (IN capital DOUBLE , IN rate INT , IN duration INT , OUT interest DOUBLE)
BEGIN
SET interest = (capital * rate * duration)/100;
INSERT INTO audit_table(interest) VALUES(interest);
END //
DELIMITER ;
(We have first changed delimiter as // to mark end of stored procedure, and then reverted it back to previous delimiter.)
This will create a stored procedure with name sp_test, it take capital , rate and duration as input and return the interest in output variable after calculating it, it also insert the interest into database.
Let's run it.
mysql > call test(500 , 1.3 , 3 , @out_interest);
mysql > select @out_interest;
@out_interest will give you the interest calculated by the stored procedure 'sp_test'. In above case 'select @out_interest;' will return '19.5'.
We can use input parameter in the query written within stored procedure. e.g:
DELIMITER //
CREATE PROCEDURE new_sp_test (IN order_id VARCHAR(255))
BEGIN
SELECT * FROM Order WHERE Order.order_id = order_id;
END
END //
DELIMITER ;
How can we declare a variable in stored procedure?
We can declare variables inside a stored procedure after BEGIN tag. In mysql we use DECLARE tag for declaring a variable. We have to specify the data type of the variable to declare the variable.
Scope of the variable in mysql is limited to END tag, that means after END tag variable will be lost.
e.g:
DECLARE i INT(3)
DECLARE j INT(9) DEFAULT 6;
We can initialize the variable after declaration, e.g:
DECLARE i INT(3);
SET i = 10;
In mysql we can take he value in the variable from the query also from the help of INTO keyword. e.g:
DECLARE orderId VARCHAR(23);
SELECT order.order_id INTO orderId FROM order;
Condition statement in Mysql programming will give us power to execute code on the basis of the some value. e.g:
DECLARE order_count INT(5) default 0;
SELECT count(*) INTO order_count FROM order;
IF order_count > 5 THEN
SELECT * FROM order;
ELSE
SELECT 'Less than 5 orders';
END IF;
Always remember, for every IF statement in Mysql we have to specify END IF statement. In mysql we can also use ELSEIF for the recursive statements. e.g:
DECLARE order_count INT(5) default 0;
SELECT count(*) INTO order_count FROM order;
IF order_count =0 THEN
SELECT 'No orders present';
ELSEIF order_count > 5 THEN
SELECT * FROM order;
ELSE
SELECT 'Less than 5 orders';
END IF;
We can also use switch case based conditional statement in mysql. e.g:
CASE
WHEN i > 2 THEN
SELECT 'greater then two';
WHEN i < 2 THEN
Select 'less then two';
ELSE
SELECT 'other case';
END CASE;
Every CASE must be closed with END CASE.
How to implement error handling in Stored Procedure?
When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful error message.
The TRY CATCH construct allows you to gracefully handle exceptions in SQL Server. To use the TRY CATCH construct, we first place a group of Transact-SQL statements that could cause an exception in a BEGIN TRY...END TRY block and then you use a BEGIN CATCH...END CATCH block immediately after the TRY block.
If the statements between the TRY block complete without an error, the statements between the CATCH block will not execute. However, if any statement inside the TRY block causes an exception, the control transfers to the statements in the CATCH block.
BEGIN TRY
-- statements that may cause exceptions
END TRY
BEGIN CATCH
-- statements that handle exception
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState
END CATCH
The CATCH block functions
- Inside the CATCH block, we can use the following functions to get the detailed information on the error that occurred.
- ERROR_LINE() returns the line number on which the exception occurred.
- ERROR_MESSAGE() returns the complete text of the generated error message.
- ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
- ERROR_NUMBER() returns the number of the error that occurred.
- ERROR_SEVERITY() returns the severity level of the error that occurred.
- ERROR_STATE() returns the state number of the error that occurred.
@@ERROR Function in SQL Server
The @@ERROR system function is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection.
If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designated error code. To determine if a statement executes successfully, an IF statement is used to check the value of the function immediately after the target statement executes.
One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.
USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
BEGIN
PRINT 'Error Occurred'
END
RAISERROR in SQL Server
We can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application. The following information is given about the parameters.
- @msg_id - This is the message ID of your user-defined message. All adhoc error messages are given the message ID of 50000.
- @msg_str - The message can have up to 400 characters. If the message contains more than 400 characters, only the first 397 will be displayed and an ellipsis will be added to indicate that the message has been cut. You can also use formatting values within the message text. An example of this is given below.
- @argument - These are values to be used within the message text.
- @WITH - Acceptable values are LOG, NOWAIT, and SETERROR. The LOG option logs the error to the server and application log; NOWAIT sends the messages immediately back to the client application; and SETERROR sets the @@ERROR value to the @MSG_ID or 50000, regardless of the serverity level.
USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
BEGIN
RAISERROR ('An error occured updating the NonFatal table',10,1)
END
-K Himaanshu Shuklaa..
Also Read:
Copyright © 2014 - ScrutinyByKHimaanshu
No comments:
Post a Comment