Skip to main content

Error Escalation and it's cure


 Error Escalation In SQL Server:

What is Error Escalation?

In DBMS, error could stand on multiple levels, let say database storage level(file path missing, change in disk, corrupt files etc), server connectivity error ( wrong instance connection, unauthorised access error, restricted users activity error), table level or data level error( data truncation, mismatch data type, out of storage etc).

These are some common error which by default occurs and must be rectified after receiving it. But some database error that are cause by the queries written on the database could be recognised before execution and proper solution can be created in order to get rid of the error in the runtime of query.


Let's understand the topic with an example:

If say, the dynamic query is written for checking the existence of any object where object name is the user parameter, in such case may be user provide an input with symbols or keyboard characters which can cause the error, so here we will see how error escalation will help in get rid of user inputs for error.

Declare @user_input varchar(25)

Declare @query varchar (max)

Set @query='select * from sys.objects where name ='+@user_input

Exec(@query)

The above query runs very fine if the user give correct parameters but let suppose user entered the value as:

Set @user_input= ;[abcd];go

Now here 'go' will be treated as a keyword in SQL as well as semi colon will be treated as special character to end the statement , so here error is escalated by the user which on runtime can generate enough logs for error as well as use multiple resources to do so.

To get rid of the error one can use Try.. catch block and tell user to pass correct parameter value.

Begin try

Declare @user_input varchar(25)

Set @user_input= ;[abcd];go

If @user_input like ';%' or  @user_input like ' ' or  @user_input like '[€¥$√π^¢%] 

  Throw 51000, 'wrong input please follow the input value rules,1;

End try 


Begin catch

Print 'pass the value again'

End catch


With such art, we can save error to be escalated.


Thankyou, for any queries please post in comments.



Comments

Post a Comment

Popular posts from this blog

How to be a DATA Analyst

To become a data analyst one need to be good at maths, basically numbers and visuals are two things every data analyst must know about. In my experience I believe domain knowledge and understanding the business is one of the key factor one would be needing to sort out pattens or analysis from the business data. Until and unless I don't know what my details is telling to me how I can be sure what to analyse. After all these basically a series of tool is important so that the work of analyst becomes easy, let's say tools like Excel, SQL , visualisation (tableau, powerbi), cloud computing (azure,AWS), modules in python like matplotlib, scikit learn, seaborn, pandas are some of the basic necessities that need to be fullfill. Some guidelines if I have to say is: 1) always try with clean data, then move to dirty data(mostly wrong columns values, mismatch column values, redundant data) 2) making quick visuals are always a better approach to increase confidence and skill in the path 3)...
Post a Comment
Read more

SQL Interview Question

  SQL Interview Question: You are given two unrelated tables: Product — containing product details ProductSubcategory — containing subcategory details There are no common columns , and no foreign key or primary key relationships between them. Task: Write a SQL query to perform a join between these two tables, despite having no direct relationship. Query And Related Table: The problem with the above script is when you have exact match between the "product description" column and "subname" column then this code will work, but let say you have difference like Gadget and Gadgets then in this case the query will fail.  Feel free to post you comments over this solution, my approach would be: 1) Do the match over the letter by letter then count the total letter matched and then total letters if the percentage for this is above 80% then this record must be in the join condition. Basically perform the lookup with the help of SQL code. 2) Make abridge...
Post a Comment
Read more

HTTP request to get data import into Database

What is HTTP request? It's a logic that can be used in order to pull data using api's created by individuals both private and public. Some API's are build in house to use it for own use and some are public API's, there are series of methods that can used in order to fetch data using these public API's. Let's see some of them: 1) GET: it is used to request some data from the frontend, usually the website URL, access token is required and in the URL itself the path is specified that someone is looking for. Let's understand with an example:- For a college website xyzuniversity.com, if I need to call the record for a student name "Alex" I will be writing the URL like GET http://university.com/student-record/alex:? And in the response I will be getting the associated record in JSON format. Here we used the GET method to call this data, like this we got multiple methods like PUT, HEAD, DELETE, PATCH,OPTIONS etc that can be used based on the need. Let...
Post a Comment
Read more