Thursday, December 29, 2011

SQL : Condition for all or fixed values

Select Emp_Code, Emp_Name, Emp_Address, Emp_Telephone, Emp_Postcode
From Employees A
Where (A.Emp_Code = @Emp_Code or isnull(@Emp_Code,'')='')

Friday, December 9, 2011

SQL : Each Null has Different Value

IF NULL = NULL
BEGIN
    SELECT 'Are Equal'
END
ELSE
BEGIN
    SELECT 'Are Not Equal'
END

Query Result is : Are Not Equal

This means that in SQL each Null has different value.










Thursday, December 8, 2011

Search Specific Word in Database


-- EXEC SearchWordInDatabase 'Test'

CREATE PROC SearchWordInDatabase
(
    @SearchWord nvarchar(100)
)
AS
BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchWord2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchWord2 = QUOTENAME('%' + @SearchWord + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
   
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchWord2
                )
            END
        END   
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

Wednesday, December 7, 2011

SQL : Numeric Order By

SELECT EmployeeID, FirstName, LastName, Address1, Address2, Telephone FROM dbo.Employees
ORDER BY 2

--This query will show your data by FirstName as it takes the Column no. 2 to sort.

Thursday, December 1, 2011

Get your's Birthday details Year, Quarters, Months, Weeks, Days, Hours, Minutes & Seconds in SQL

DECLARE @StartDate SMALLDATETIME

SET @StartDate = '01/08/1985 1:35:00' -- Put Your Date of Birth & Time

SELECT DATEDIFF(yyyy, @StartDate, GETUTCDATE()) Years,
DATEDIFF(qq, @StartDate, GETUTCDATE()) AS Quarters,
DATEDIFF(mm, @StartDate, GETUTCDATE()) AS Months,
DATEDIFF(ww, @StartDate, GETUTCDATE()) AS Weeks,
DATEDIFF(dd, @StartDate, GETUTCDATE()) AS [Days],
DATEDIFF(hh, @StartDate, GETUTCDATE()) AS [Hours],
DATEDIFF(mi, @StartDate, GETUTCDATE()) AS [Minutes],
DATEDIFF(ss, @StartDate, GETUTCDATE()) AS [Seconds]

Capitalize the first character

Function :

CREATE FUNCTION InitCapital (
@string varchar(255)
)
RETURNS varchar(255) AS
BEGIN

RETURN upper(left(@string, 1)) + right(@string, len(@string) - 1)

END

Query :

SELECT
InitCapital(Left(user_name, CHARINDEX('.', user_name, 1) - 1)) as First_Name,
InitCapital(Right(user_name, (Len(id_user) - CHARINDEX('.', user_name, 1)))), as Last_Name,
username,
FROM tblUsers