October 14, 2007 at 6:57 pm
Instead of using long “select-case when-else” construction, we can use COALESCE function when we want to check if the value is NULL (this makes the code much shorter)
This is the long CASE WHEN construction:
CASE
WHEN (expression1 IS NULL) THEN 0
ELSE expression1
Here’s the use of COALESCE function
COALESCE(expression1,0)
More info
Posted in Tech Tips |
No Comments »
October 14, 2007 at 6:56 pm
Code:
daysInMonth = Day(DateSerial(thisYear, thisMonth + 1, 0))
It adds 1 to the month and uses DateSerial to get a Date representing the 0th day of the following month. That is the last day of the month you entered. The Day function gives you the number of that day, which equals the number of days in the month.
Posted in Tech Tips |
No Comments »
October 14, 2007 at 6:50 pm
Well, as I’m posting my tips here, I came across the problem with pasting programming code. Now I’m looking for a good online utility that would take code and HTML-escape it. It would be even better if it also did color-coding.
If I find something good, I’ll post a link here. For now, I’ll just be using a utility to escape all > and < entities. This is the first one I found in Google.
Posted in Tech Tips |
No Comments »
October 14, 2007 at 6:41 pm
I needed to break a string like this “Hot Waffles,Fresh Fruits,Pastries,Bagels,Cereals,Muffins,Juices,Milk,Coffee” into separate XML elements. How to do it? Use a “tokenize” function!
<xsl:template name=”breakfast”>
<xsl:param name=”allItems”/>
<xsl:for-each select=”tokenize($allItems, ‘,’)”>
<item><xsl:value-of select=’.'/></item>
</xsl:for-each>
</xsl:template>
Result:
<item>Hot Waffles</item>
<item>Fresh Fruits</item>
<item>Pastries</item>
<item>Bagels</item>
<item>Cereals</item>
<item>Muffins</item>
<item>Juices</item>
<item>Milk</item>
<item>Coffee</item>
Posted in Tech Tips |
No Comments »
October 14, 2007 at 6:39 pm
select top 10 * from table order by newid()
Posted in Tech Tips |
No Comments »
October 14, 2007 at 6:37 pm
When there’s a large amount of data to be deleted, the performance of SQL suffers a lot. One way around it is to delete in batches, for example 10 000 rows at a time.
Instead of :
delete from yourtable where [condition]
i’d use:
– set rowcount to delete sets of 10 000 records (use a smaller subset size if you think it’s better)
set rowcount 10000
– start delete
delete from yourtable where
while @@rowcount > 0 — loop until there’s no record left to delete
begin
— after each delete there will be an implicit commit
delete from yourtable where
end
Posted in Tech Tips |
No Comments »
October 14, 2007 at 6:33 pm
To save as Excel CSV file:
Response.ContentType=”application/csv”
Response.AddHeader “Content-Disposition”, “filename=myfile.csv”
To save as any type:
Response.ContentType=”application/x-msdownload”
Response.AddHeader “Content-Disposition”, “filename=sitemap.xml”
Posted in Tech Tips |
No Comments »
October 14, 2007 at 6:31 pm
Word Cleaner (online, in a form).
Puretext (needs installation).
More info on saving from Word into HTML.
Posted in Tech Tips |
No Comments »
October 14, 2007 at 6:29 pm
upperlimit = 10
lowerlimit = 0
r = Int((upperlimit - lowerlimit + 1)*Rnd() + lowerlimit)
Posted in Tech Tips |
No Comments »
October 11, 2007 at 3:30 pm
VBScript:
If LCase(request.ServerVariables(”HTTPS”)) = “off” Then
response.write “Error: access denied”
response.end
End If
JavaScript:
if(location.protocol != “https:”)
{
alert(”Error”);
}
Posted in Tech Tips |
No Comments »