Tag 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:

UPDATE Product
SET Product.Price = Import_Prices.Price,
    Product.Discount = Import_Prices.Discount
FROM Product INNER JOIN Import_Prices
   ON Product.ID = Import_Prices.ProductID

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:

SELECT * FROM SomeTable ORDER BY @Variable

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:

CREATE PROCEDURE uspGetOrderedItems
(
  @sortField VARCHAR(100) = 'ItemName',
  @sortDirection VARCHAR(4) = 'DESC'
)
AS
BEGIN         
  SET NOCOUNT ON

  SELECT * FROM Item 

  ORDER BY 
    (CASE WHEN @sortField = 'ItemName' AND @sortDirection = 'ASC' THEN ItemName END) ASC,
    (CASE WHEN @sortField = 'ItemName' AND @sortDirection = 'DESC' THEN ItemName END) DESC,
    (CASE WHEN @sortField = 'Price' AND @sortDirection = 'ASC' THEN Price END) ASC,
    (CASE WHEN @sortField = 'Price' AND @sortDirection = 'DESC' THEN Price END) DESC;
END

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:

CREATE PROCEDURE uspGetOrderedItemsWithPaging
(
  @sortField VARCHAR(100) = 'ItemName',
  @sortDirection VARCHAR(4) = 'DESC',
  @startRow int = 1,
  @endRow int = 20
)
AS
BEGIN         
  SET NOCOUNT ON

  SELECT * FROM 
    (
    SELECT ROW_NUMBER() OVER (	
      ORDER BY 
        (CASE WHEN @sortField = 'ItemName' AND @sortDirection = 'ASC' THEN ItemName END) ASC,
        (CASE WHEN @sortField = 'ItemName' AND @sortDirection = 'DESC' THEN ItemName END) DESC,
        (CASE WHEN @sortField = 'Price' AND @sortDirection = 'ASC' THEN Price END) ASC,
        (CASE WHEN @sortField = 'Price' AND @sortDirection = 'DESC' THEN Price END) DESC
      ) AS ROW_NUM, *
    FROM Item
    ) Result
    WHERE ROW_NUM BETWEEN @startRow AND @endRow;

END

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:

SELECT DATEADD(dd, 2, SomeDate) FROM SomeTable;

 

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

Syntax of DATEADD function is:

DATEADD( TimeIntervalName, Value, FieldOrVariable )

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.