SQL- Using parameter in Select … Like statement

You need to concatenate wildcard:

string sql = "Select Top 5 ExamName From Qualifications Where ExamName Like '%' + @ExamName + '%'";

//If you use SQL Server 2012+ use:
string sql = "Select Top 5 ExamName From Qualifications Where ExamName Like CONCAT('%', @ExamName, '%')";
Advertisements

SQL – Select Parent and Count Child from same table


//Let's say, you have a table as follows-
--------------------------------------------
 DesignationId | DesignationName| ParentId |
 -------------------------------------------
    1          |     A          |    0     |
 -------------------------------------------
    2          |     B          |    1     |
 -------------------------------------------
    3          |     C          |    1     |
 -------------------------------------------


// You would like to select records as follows
--------------------------------------------
 DesignationId | DesignationName| Childs   |
 -------------------------------------------
    1          |     A          |    2     |
 -------------------------------------------
    2          |     B          |    0     |
 -------------------------------------------
    3          |     C          |    0     |
 -------------------------------------------

Here is the SQL Statement-

SELECT DesignationId AS Id, DesignationName as Name, 
(SELECT COUNT(*)  FROM Designations AS T WHERE T.ParentID = O.DesignationId) 
FROM Designations AS O Where O.DesignationId>0

SQL Server Bulk Copy in C# Application.

using (SqlConnection connection = new SqlConnection(ConString))
{
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "dbo.DestinationTable";
        try
        {
            connection.Open();
            // Write from the source to the destination.
            bulkCopy.WriteToServer(tblSourceTable);
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString()); return;
        }
    }
}

Get Hourly Data from SQL Server DateTime Field


SELECT DATEPART(dd, SurveyorActivityDateTime) AS myday, 
       DATEPART(hh, SurveyorActivityDateTime) AS myhour, 
       Count(RetailerId) AS Quantity
FROM [RobiPosMapping].[dbo].[Retailer]
WHERE CAST(SurveyorActivityDateTime AS DATE) = '2015-06-04'
GROUP BY DATEPART(dd, SurveyorActivityDateTime), 
         DATEPART(hh, SurveyorActivityDateTime)

Basic Trigger Example

/*Create or edit a trigger */
CREATE/ALTER TRIGGER triggerName
ON [sourceTableName]
FOR INSERT/UPDATE/DELETE
AS
Begin
    INSERT INTO targerTableName(column1 [,column2]) 
    SELECT i.column1 [,i.column2]   /* d.column1  (if you want to preserve prev value)*/
    FROM Inserted i                 /* FROM deleted d  */
End


/*Get list of triggers */
SELECT * FROM sys.triggers WHERE is_disabled = 0