Category Archives: T-SQL

T-SQL Update Table From Another Table

Let say you have two tables and you want to update values in first table with values from second table. That could be for example if you have Product table and you have to load new prices and discounts from Import_Prices table.

The T-SQL code to update data could look like this:

In this case column ID of table Product corresponds to column ProductID of table Import_Prices .

Be careful to define relationship correctly and eventually filter only needed rows with WHERE clause. UPDATE command could change all table rows if you don’t narrow it correctly, so be sure that you have backup created before using ad-hoc UPDATE query :)

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.

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.