Try Catch in .NET

The Try word means “Try to execute this code”. The Catch word means “Catch any errors here”. The ex is a variable, and the type of variable it is an Exception object.

Example

Public Function Delete(ByVal fileName As String) As Boolean
	Try
		File.Delete(String.Format("{0}\{1}", ConfigurationManager.AppSettings("ImageDirectory"), fileName))
		Return True
	Catch ex As Exception
		Return False
	End Try
End Function

SELECT RIGHT

RIGHT
Returns the part of a character string starting a specified number of integer_expression characters from the right.

Example

CREATE TABLE #CustomerNames
(
	CustomerId int,
	CustomerName varchar(25)
)	

INSERT INTO #CustomerNames
SELECT 1, 'Bob Johnson'
UNION ALL
SELECT 2, 'Al Riley'
UNION ALL
SELECT 3, 'Mike Henry'

SELECT RIGHT(CustomerName, 5) FROM #CustomerNames

Results
hnson
Riley
Henry

SELECT WHERE ANY

SELECT *
FROM VTM_DATA_DETAILS
WHERE (PRO_NUMBER = ANY
(SELECT pro_no
FROM vtm_data_combination
WHERE (total_weight = 0) AND (total_charges = 0) AND (total_pieces = 0)))

This query is essentially, imagining PRO_NUMBER is a 3 digit number:

SELECT *
FROM VTM_DATA_DETAILS
WHERE (PRO_NUMBER = 097) OR (PRO_NUMBER = 647) OR (PRO_NUMBER = 324)…..etc.

Until all PRO_NUMBER are selected from vtm_data_combination.

IsNumeric function

The function returns 1 for numeric values, and 0 for non-numeric values.

CREATE TABLE #CustomerNames
(
	CustomerId int,
	CustomerName varchar(25)
)	

INSERT INTO #CustomerNames
SELECT 1, 'Bob Johnson'
UNION ALL
SELECT 2, 'Al Riley'
UNION ALL
SELECT 3, 'Mike Henry'

SELECT ISNUMERIC(CustomerID), ISNUMERIC(CustomerName) FROM #CustomerNames

Results
1     0
1     0
1     0

Convert from varchar to decimal

Here’s a way to convert a VARCHAR to DECIMAL, wrap the CONVERT in a CASE and IsNumeric function to make sure that it doesn’t cause errors when the varchar data is non-numeric

DECLARE @test VARCHAR(25)

--Numeric
SET @test = '2.55'
SELECT CONVERT(DECIMAL(3,2), CASE WHEN ISNUMERIC(@test) = 1 THEN @test ELSE '0' END)

--Non-Numeric
SET @test = 'Bob'
SELECT CONVERT(DECIMAL(3,2), CASE WHEN ISNUMERIC(@test) = 1 THEN @test ELSE '0' END)

Results
—————————————
2.55

—————————————
0.00

SQL Server Datatypes

Data Types

  • sql_variant: Stores values of various
    SQL Server-supported data types, except text, ntext, and timestamp.
  • timestamp: Stores a database-wide
    unique number that gets updated every time a row gets updated.
  • uniqueidentifier: Stores a globally
    unique identifier (GUID).
  • xml: Stores XML data. You can
    store xml instances in a column or a variable (SQL Server 2005 only).
  • cursor: A reference to a cursor.
  • table: Stores a result set for later
    processing.