Nov 5, 2013

How to check a given data or column is type of integer or not in sql server?

Sql server doesn’t support a function like IsInteger.  We can test a given data or column is type of int by using the function SQL_VARIANT_PROPERTY. For example:

SELECT SQL_VARIANT_PROPERTY(25,'BaseType')
Output: int

Creating IsInteger function in sql server

CREATE FUNCTION IsInteger(
    @Data AS SQL_VARIANT
)
RETURNS BIT
AS
BEGIN

    DECLARE @IsInt AS BIT
   
    IF SQL_VARIANT_PROPERTY(@Data,'BaseType') = 'int'
         SET  @IsInt = 1
    ELSE
         SET  @IsInt = 0
        
    RETURN @IsInt
   
END

Examples:
DECLARE @Var1 INTEGER = 25
DECLARE @Var2 VARCHAR(5) = '25'
DECLARE @Var3 BIT = 1
DECLARE @Var4 BIGINT = 25

SELECT
    dbo.IsInteger(@Var1),
    dbo.IsInteger(@Var2),
    dbo.IsInteger(@Var3),
    dbo.IsInteger(@Var4)

Output:
1   0   0   0

Note: Maximum and minimum value of integer constant in sql server is 2147483647 and -2147483648 respectivily. Any number lager or smaller than this values are not integer constant. For example:

SELECT
    dbo.IsInteger(2147483647),
    dbo.IsInteger(-2147483648),
    dbo.IsInteger(2147483648),
    dbo.IsInteger(-2147483649)

Output:
1   1   0   0

Nov 2, 2013

BETWEEN keyword in sql server

BETWEEN is a range tester which can be used either in WHERE clause or HAVING clause. 

Syntax:
<Test_Expression>  [NOT]  BETWEEN <Initial_Expression>  AND  <Ending_Expression>

Where:
<Test_Expression>: It any valid expression which to be test that it value is within the <Initial_Expression> and <Ending_Expression> or not.  It is commonly name of the column of a table.

<Initial_Expression>  and  <Ending_Expression>: It is the boundary values.

NOT: It is optional keyword which negates the meaning.

Return type: It return a Boolean value.

Note:  Data type of <Test_Expression>, <Initial_Expression> and <Ending_Expression> must be same or implicitly convertible.

For example:  Suppose RollNo is column of type integer

RollNo BETWEEN 1 AND 100

Above statement will return true if and only if   1 <= RollNo <= 100

RollNo NOT BETWEEN 1 AND 100

Above statement will return true if and only if    RollNo   < 1 or RollNo > 100

Consider we have Employee table which look like this:
EmpID
EmpName
DateOfJoining
Age
100
Sachin Tendulker
2008-12-05
35
101
Ricky Ponting
2001-06-08
40
102
Brian Lara
2000-03-28
42
103
Stephen Fleming
1987-11-01
54
104
Shaun Pollock
2001-11-22
32
105
Shane Warne
1988-04-21
51
106
Shoaib Akhtar
1990-09-17
42
107
Muttiah Muralitharan
2002-07-07
36
108
Andrew Flintoff
2004-05-30
33

Using BETWEEN with varchar:

SELECT *
FROM Employee
WHERE EmpName BETWEEN 'Sho' AND 'T'

BETWEEN statement will return true if and only if EmpName is start from Sho upto T same as dictionary. It is not case sensitive.

Output will be:
EmpID
EmpName
DateOfJoining
Age
103
Stephen Fleming
1987-11-01
54
106
Shoaib Akhtar
1990-09-17
42

Using BETWEEN with date:

SELECT *
FROM Employee
WHERE DateOfJoining BETWEEN '2001-11-22' AND '2008-12-05'

It will select all the employees whose DataOfJoining is between 2001-11-22 to  2008-12-05 including 2001-11-22 and 2008-12-05

Output will be:
EmpID
EmpName
DateOfJoining
Age
100
Sachin Tendulker
2008-12-05
35
104
Shaun Pollock
2001-11-22
32
107
Muttiah Muralitharan
2002-07-07
36
108
Andrew Flintoff
2004-05-30
33

Using BETWEEN in having clause:

SELECT Age,COUNT(*) AS Total
FROM Employee
GROUP BY Age
HAVING Age BETWEEN 40 AND 50

Output:
Age
Total
40
1
42
2