It is not straight forward to create an atomic insert or update statement in SQL Server, i.e. we update the row if it exists. Otherwise we insert a new row. The following statement (notice the locks) will create a more or less atomic statement: In the code above PK is the primary key that will […]
MS SQL: URL Encode a string in Transact-SQL
There is no built-in function in Microsoft SQL Server to to support URL encoding. If you want to generate URLs with arguments on the fly in stored procedures, you would have to do the URL encoding yourself. URL encoding can be implemented in several ways. Either you can create your own custom userdefined function in […]
MS SQL: Create MD5 Hash string in Transact-SQL
The non-reversable hashing algorithm MD5 is supported in Microsoft SQL Server, but is not directly accessable though a simple transact-SQL function. Normally you would store the MD5 hash value as a hexadecimal string in your database. The HashBytes function returns a binary array of hash data. To convert the binary data to hex we need to use the function fn_varbintohexstr. To […]
MS SQL: Defining Constants in Transact-SQL
Transact-SQL does not offer a good way to support constants in your stored procedures or userdefined functions. This means that you either can choose to hardcode your constants or, the more elegant way, define a userdefined function for each “constant” value. To ensure optimal performance, the schemabinding keyword should be used. Here is an example: In your stored procedure […]
MS SQL: Get the Time portion from a DateTime
To get only the Time portion of a DateTime variable in Transact-SQL, you can use the following function: Please notice that the base date is January 1st 1900. Any dates/times prior to this day won’t work, Releated How to get the Date portion of a DateTime in Transact-SQL? How to create a Date in Transact-SQL?
MS SQL: Create a Date in Transact-SQL
There is no straight forward function in Transact-SQL to create a simple date based on year, month and day. Below we have created a userdefined function to handle this. Notice that the base date in Microsoft SQL Server is January 1st 1900. We generate the date by adding the year, month and day to the base […]
MS SQL: Get the Date portion from a DateTime
To return only the date portion of a DateTime variable in Microsoft SQL server, you need to use a combination of the dateadd and datediff functions in Transact-SQL. Releated How to get the Date portion of a DateTime variable in Transact-SQL? How to create a Date in Transact-SQL?
Excel VBA: Send an E-mail from Excel
There are several ways to send e-mail from Excel using Microsoft Outlook. It is possible to use the built-in function ActiveWorkbook.SendMail. However, it only allows simple e-mails to be created, and there is no way to add attachments. In this example we are going to use the COM library of Microsoft Outlook to get more control. […]
Excel VBA: Get all the Selected rows
If the user selects multiple rows in Microsoft Excel, the selection can contain multiple areas. An area is a subselection of rows created when the user presses the Shift button while doing a multiselect. In VBA we need to handle each area independently to be able to retrieve all the selected rows. In the example we have […]
Excel VBA: How to Expand and Collapse a Group
In Microsoft Excel it is possible to group rows and columns. To expand or collapse a group you can use the VBA method called ShowLevels. In the example below we are expanding a row group. We have also disabled automatic recalculation to improve the performance. Please notice that expand and collapse work in the same way. Collapsing a […]