SQLFacts: Injection in SQL SERVER
SQLFacts: Injection in SQL SERVER: SQL injection is a technique where hacker can inject SQL commands into an SQL statement, via web page.Injected SQL commands can comprom...
SQLFacts: SQL Server Exception Handling by TRY…CATCH
SQLFacts: SQL Server Exception Handling by TRY…CATCH: SQL Server Exception Handling by TRY…CATCH Like C#, SQL Server also has an exception model to handle exceptions and errors that occur...
SQL Server Exception Handling by TRY…CATCH
SQL Server Exception Handling by TRY…CATCH
Like C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH
blocks. In Sql Server, against a Try block we can have only one CATCH block.
blocks. In Sql Server, against a Try block we can have only one CATCH block.
TRY..CATCH Syntax
BEGIN TRY
--SQL statements
--or SQL statement blocks
END TRY
BEGIN CATCH
--SQL statements
--or SQL statement blocks
END CATCH
Error Functions used within CATCH block
1. ERROR_NUMBER()
This returns the error number and its value is same as for @@ERROR function.
2. ERROR_LINE()
This returns the line number of T-SQL statement that caused error.
3. ERROR_SEVERITY()
This returns the severity level of the error.
4. ERROR_STATE()
This returns the state number of the error.
5. ERROR_PROCEDURE()
This returns the name of the stored procedure or trigger where the error occurred.
6. ERROR_MESSAGE()
This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
Exception handling example
BEGIN TRY
DECLARE @num INT, @msg varchar(200)
---- Divide by zero to generate Error
SET @num = 5/0
PRINT 'This will not execute'
END TRY
BEGIN CATCH
PRINT 'Error occured that is'
set @msg=(SELECT ERROR_MESSAGE())
print @msg;
END CATCH
GO
1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.
2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.
3. Each TRY block is associated with only one CATCH block and vice versa
4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.
5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.
6. XACT_STATE function within the TRY..CATCH block can be used to check whether a open transaction is committed or not. It will return -1 if transaction is not committed else returns 1.
SQL Server Exception Handling
SQL Server Exception Handling by TRY…CATCH
Like C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks. In Sql Server, against a Try block we can have only one CATCH block.
TRY..CATCH
Syntax
BEGIN TRY
--SQL statements
--or SQL statement blocks
END TRY
BEGIN CATCH
--SQL statements
--or SQL statement blocks
END CATCH
Error
Functions used within CATCH block
1. ERROR_NUMBER()
This returns the error
number and its value is same as for @@ERROR function.
2. ERROR_LINE()
This returns the line number
of T-SQL statement that caused error.
3. ERROR_SEVERITY()
This returns the severity
level of the error.
4. ERROR_STATE()
This returns the state
number of the error.
5. ERROR_PROCEDURE()
This returns the name of the
stored procedure or trigger where the error occurred.
6. ERROR_MESSAGE()
This returns the full text
of error message. The text includes the values supplied for any substitutable
parameters, such as lengths, object names, or times.
Exception
handling example
BEGIN TRY
DECLARE @num INT, @msg varchar(200)
---- Divide by zero to generate Error
SET @num =
5/0
PRINT 'This will
not execute'
END TRY
BEGIN CATCH
PRINT 'Error
occured that is'
set @msg=(SELECT ERROR_MESSAGE())
print @msg;
END CATCH
GO
1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.
2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.
3. Each TRY block is associated with only one CATCH block and vice versa
4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.
5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.
6. XACT_STATE function within the TRY..CATCH block can be used to check whether a open transaction is committed or not. It will return -1 if transaction is not committed else returns 1.
SQL Bulk insert using xml
If we need to insert the data into a table from xml file
then we have below options
1) By for loop in our
business logic /code behind: this approach will time consuming in case of we
have a big xml file.
2) By using Store procedure to insert bulk record
into Database
Assume we have this xml file
<?xml version="1.0" encoding="utf-8"?>
<Employees>
<Employee >
<Eid>11</Eid>
<Name>John Smith</Name>
<Designation>Team Leader</Designation>
<Salary>50000</Salary>
<DeptNo>1</DeptNo>
</Employee>
<Employee >
<Eid>12</Eid>
<Name>Sumit</Name>
<Designation>Admin</Designation>
<Salary>1000</Salary>
<DeptNo>2</DeptNo>
</Employee>
<Employee >
<Eid>13</Eid>
<Name>John</Name>
<Designation>Sr. Software Devoloper</Designation>
<Salary>2000</Salary>
<DeptNo>2</DeptNo>
</Employee>
</Employees>
<Employees>
<Employee >
<Eid>11</Eid>
<Name>John Smith</Name>
<Designation>Team Leader</Designation>
<Salary>50000</Salary>
<DeptNo>1</DeptNo>
</Employee>
<Employee >
<Eid>12</Eid>
<Name>Sumit</Name>
<Designation>Admin</Designation>
<Salary>1000</Salary>
<DeptNo>2</DeptNo>
</Employee>
<Employee >
<Eid>13</Eid>
<Name>John</Name>
<Designation>Sr. Software Devoloper</Designation>
<Salary>2000</Salary>
<DeptNo>2</DeptNo>
</Employee>
</Employees>
Then the Store procedure to insert
bulk record into Database will be
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SpEmpDetailsIns]
(@xmlstr ntext)
as
begin
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
insert into tbl_xml_Emp
select xml.Eid,xml.name,xml.Designation,xml.Salary,xml.Deptno
from OPENXML(@hDoc,'/Employees/Employee',2)
with(Eid int,
name varchar(50) 'Name',
Designation varchar(50) ,
Salary money,
Deptno int 'DeptNo')xml
exec sp_xml_removedocument @hDoc
end
1.
Format your XML to look like
<Employee >
<Eid>1001</Eid>
<Name>BBB</Name>
<Designation>Software Devoloper</Designation>
<Salary>30000</Salary>
<DeptNo>20</DeptNo>
</Employee>
did you notice how <Employee> element have the column as children and each column value is a node text not an attribute.
Then you will need to use "2" in the OPENXML function call
from OPENXML(@hDoc,'/Employees/Employee',2)
<Employee >
<Eid>1001</Eid>
<Name>BBB</Name>
<Designation>Software Devoloper</Designation>
<Salary>30000</Salary>
<DeptNo>20</DeptNo>
</Employee>
did you notice how <Employee> element have the column as children and each column value is a node text not an attribute.
Then you will need to use "2" in the OPENXML function call
from OPENXML(@hDoc,'/Employees/Employee',2)
2.
<Employee Eid="1001" Name="BBB"
Designation="Software Devoloper" Salary="30000"
DeptNo="20"> </Employee>
No change required for the OPENXML function call it remains same as before
No change required for the OPENXML function call it remains same as before
Subscribe to:
Posts (Atom)