Update statement with case statement in SQL Server

It is very important to know that how case statement will behave when used in Update query.
As we know case statement can have multiple "when" and else is not required.
So if the query is executed where no condition is met defined in case statement then the row will be updated to null value. So to avoid this, you need else statement with whatever value you want to keep.

See example below:

'create table
CREATE TABLE #test (worktime DATETIME)

' insert current time
INSERT INTO #test( worktime ) VALUES  ( GETDATE());

' check value
SELECT * FROM #test

' now write case statement with no else and not meeting any condition

UPDATE #test

SET worktime = CASE WHEN worktime IS NULL THEN GETDATE() 

                                   WHEN worktime < '1/1/2010' THEN GETDATE()-7 

END

Check the value in table, the row will be updated to null.

So always mention else part of case statement when using case.

HTH,

Use EWS (Exchange Web Services) to send emails

EWS (Exchange Web Services) is an API that you can use to interact with Exchange items such as mail, calendar, contacts etc.

You can also use Outlook Object model to send emails. 

To work with EWS, you have install EWS (it only works with 64 bit machine). The link to download is here

Here is the sample code to Send an email - 

' import namespace
Imports Microsoft.Exchange.WebServices.Data

Public Class TestMail

    Public Sub SendMailusingEWS()

        Dim service As ExchangeService = New ExchangeService(ExchangeVersion.Exchange2010_SP1)

        'Connect to Exchange Web Services as testaccount at mydomain.com.
        service.Credentials = New WebCredentials("testaccount", "password", "mydomain")

        'OR try this if above doesn't work. This credential depend how AD is configured. 
        'service.Credentials = New WebCredentials("testaccount@mydomain.com", "password")

        ' This is to bypass server certificate. ONLY FOR DEBUGGING. May not required.
        System.Net.ServicePointManager.ServerCertificateValidationCallback = Function(sender, certificate, chain, sslPolicyErrors) True

        'Set the URL of the property of service.
        service.Url = New System.Uri("https://myexchange.mydomain.com/EWS/Exchange.asmx")

        'For exchange365
        'service.Url = New System.Uri("https://outlook.office365.com/EWS/Exchange.asmx")

        'OR you can also set the URL by calling autodiscoverURL. You might want to cash it and define as above.
        'service.AutodiscoverUrl("testaccount@mydomain.com")

        'Create msg object and set its connection by passing service object  

        Dim msg As EmailMessage = New EmailMessage(service)
        msg.Subject = "test from EWS service"
        msg.Body = "saqib is testing"
        msg.ToRecipients.Add("mymail@gmail.com")
        msg.SendAndSaveCopy()

    End Sub

End Class