Monthly Archives: December 2014

T-SQL Dynamic Order By

When you have some list of records in web or Windows application, sooner or later your client will say: Hey could you make columns sortable? It would be great if I could just click on header and order rows?

When coders try to implement dynamic Order By in T-SQL, first logical approach is to create variable and place column name in it. For example:

It seems logical on first sight, but this approach is not working. Variable is not understood as column, but as string value. Thus, rows will be ordered by constant. In other words, rows will not be ordered on any way.

So, what to do? Fortunately, there is better solution than writing several same queries with different ORDER BY clause. Check this example with using of CASE:

One more variation of same idea is to use ROW_NUM() function. If table contains lots of records, it’s better to provide paging. It’s usually bad idea to show thousands or millions of records in grid. For paging and ordering at same time, we can use ROW_NUMBER() function.

Here is example stored procedure which returns data, both paged and ordered, based on 4 input parameters:

In addition to these 4 parameters, based on your business needs you could add additional parameters which will be used in WHERE clause to filter the data.

How To Print Only Part Of Web Page

I got a request to implement print feature on page, but without opening new window. Instead of classic using of window.open and then window.print methods, all should stay on same page. Only content of one DIV tag should be printed.

After some search on Google I found solutions that use JavaScript, but personally I don’t like the idea to remove content of page to string, print what is needed and then take content back. It’s complicated and it could break something else on page.

Here is better solution to print one DIV only, without JavaScript. I organized HTML like this:

Now, add this CSS:

So, the idea is simple. Use @media print in CSS to define what will be shown in print time.

You can even create Print Preview feature on page. With JavaScript or jQuery place DIV on center of the screen. Add Print button which will call window.print(); method to print the window. Of course, only single DIV will be printed. :)

Happy coding!

Using DATEADD To Increase Or Decrease DateTime Value On SQL Server

If you want to add or subtract some time from DateTime value, SQL Server has very useful DATEADD function.

For example, to add two days to SomeDate field, use this code:

 

To subtract time, just use negative value of second parameter.

Syntax of DATEADD function is:

In previous example, d is abbreviation for day. Here is complete list of interval names and their abbreviations that can be used with DATEADD function:

Nanosecond: ns
Microsecond: mcs
Millisecond:  ms
Second:  ss or s
Minute: mi or n
Hour: hh
WeekDay: dw or w
Week: wk or ww
Day: dd or d
DayOfYear: dy or y
Month:  mm or just m
Quarter:  qq or q
Year:  yy or yyyy

Note that Nanosecond and Microsecond can be used only on SQL Server 2008 or later.

T-SQL To Create Full Text Catalog On SQL Server Express

SQL Express Management Studio has one irritating behavior when you try to create Full Text catalog. Even if you install SQL Server with advanced services and FullText is installed, Management Studio will still return an error like this:

SQL Server Express Create Full Text Catalog Error

SQL Server Express Create Full Text Catalog Error

Solution is to create Full Text Catalog directly with SQL query:

After catalog is created, you can edit in Management Studio by using right click -> Properties, where you can add tables and columns to catalog by using Management Studio GUI.

How To Implement Google Analytics Tracking in WordPress Website

Google Analytics is powerful and free tool which provides all possible information about website audience.

To install Analytics tracking code on WordPress website, there are two basic ways:

Use Google Analytics Plugin

The method is to use specialized plugin. One very popular is plugin Google Analytics by Yoast. It has a lot of options, and even a payable Premium edition. Yoast is integrated with Google Analytics and provides a lot of data inside WordPress backend.

If you are not technical person, their free version is probably method you should choose. Just install plugin from Plugins section of Dashboard, set Analytics login information and you’re done.

Insert Google Analytics tracking code directly in theme code

For me, it’s a little too much to install big plugin just to place few lines of tracking code. Yoast has additional features, like showing of Analytics data directly in WordPress Dashboard. But, I prefer to load directly to Analytics website since there I see more information.

One option is to insert tracking code to footer.php or header.php file of WordPress theme. Go to Dashboard -> Appearance -> Themes -> Editor page and click on Header on file list on right side. Place Google Analytics code just before ending </head> tag. Click Update File button to save the changes.

This option is also pretty easy and fast, and there is no additional plugin involved. However, there is one flaw. Since you edit theme file, if you update theme later to new version, that will remove tracking code too. So, you can either not update theme after you install Analytics tracking code, or add it again on same way after theme update.
Sometimes this is good enough, like when you use custom theme and have full control over update process.

Finally, I found Super Simple Google Analytics. It’s small plugin which only inserts Google tracking code on right place and nothing else. On this way you can avoid huge plugin and also Analytics tracking code will survive theme update.

How To Colorize Source Code Snippet In WordPress Post

There are lots of WordPress plugins for colorizing source code syntax, and many of them are pretty good.

Two most impressive I found are Crayon Syntax Highlighter and SyntaxHighlighter.

Both are great, and it’s hard to decide which is better. Both probably have more options than common user needs. Finally, I decided to use Crayon because it has slightly better ranking in WordPress plugins directory. Here is one small sample:

Websites hosted on WordPress.com have pre-installed syntax plugin. To place source code in article on blog hosted on WordPress.com, just use [sourcecode] or just [code] tag in text view. For previous code example if your blog is on WordPress.com you, should write something like this:

[code language=”javascript”]
// Hello World in JavaScript
alert(‘Hello World!’);
[/code]

More info about this feature you can find on http://en.support.wordpress.com/code/posting-source-code/