Parameterize Sorting Stored Procedure

In Data-driven application or website, dynamic sorting is common need. Ideally we should write stored procedure for dynamic sorting.

We found nice solution for above problem. Bellow stored procedure fetch ordered data with passed parameter. There two parameters for dynamic sorting stored procedure. Parameter @sortDirection pass for order direction (asc or desc). Second parameter @sortCol for pass sorting field name.

Code

---

=====================================================-
-- Create date:   18-Nov-2011
-- Description:   Example of Fetch Data With Sorting Parameter
=====================================================
Create PROCEDURE Product_Sorting_Parameter
    -- Add the parameters for the stored procedure here
    @sortDirection as varchar(5),
    @sortCol as varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;
    -- Select Query
    SELECT
        [ProductID] ,[Name],[ProductNumber],[Color],[ListPrice],[Size]     
     FROM         [AdventureWorks2008R2].[Production].[Product]
     ORDER BY
     -- Name
     Case WHEN @sortCol = 'Name' and @sortDirection = 'asc' THEN Name end,
     Case WHEN @sortCol = 'Name' and @sortDirection = 'desc' THEN Name end desc,
     -- Size
     Case WHEN @sortCol = 'Size' and @sortDirection = 'asc' THEN Size end,
     Case WHEN @sortCol = 'Size' and @sortDirection = 'desc' THEN Size end desc,
     -- Color
     Case WHEN @sortCol = 'Color' and @sortDirection = 'asc' THEN Color end,
     Case WHEN @sortCol = 'Color' and @sortDirection = 'desc' THEN Color end desc,
     -- Price
     Case WHEN @sortCol = 'Price' and @sortDirection = 'asc' THEN ListPrice end,
     Case WHEN @sortCol = 'Price' and @sortDirection = 'desc' THEN ListPrice end desc
END
GO

NEWID() - Generate Randomly Sort Records - TSQL

In our company, we have project for "Quiz management". On quiz page, we have to fetch 10 random question from large table. To get a random question, you might be tempted to select the top n rows from the table. However, this question is not random. The first n rows are not necessarily representative of the whole table. After few searches on MSDN, we found wonderfull solution. The solution here is the NEWID function, which generates a globally unique identifier (GUID) in memory for each row. By definition, the GUID is unique and fairly random; so, when you sort by that GUID with the ORDER BY clause, you get a random ordering of the rows in the table.

For that we use bellow query, which generate 10 random question each time. For testing we run them 3 times and code as per bellow.

Code :

SELECT TOP 10  *   FROM questions ORDER BY NEWID();

Monthwise report With Pivot Query

Yesterday One of my friend give me to solve one interesting query. There are some specific forms list in one table. There is one more table for month. The status of form as per month recorded in another table. Base on status table, we have to find for which month status record not fill up for forms.

Table Name         Field Name
Forms                   form_id
                              form_name

Months                 Month_id
                              Month_Name

Status                   form_id
                              month_id
                              status (whether filled or not)
                              comment (Comment for each entry)

Sample Data As per Bellow: 

Required Result

I found one solution for this. The code of query as per bellow.

Server Side Pagination Query

The paging of a large database resultset in Web applications is a well known problem. In short, you don't want all the results from your query to be displayed on a single Web page, so some sort of paged display is more appropriate. While it was not an easy task in the old ASP, the DataGrid control in the ASP.NET simplifies this to a few lines of code. So, the paging is easy in ASP.NET, but the default behavior of the DataGrid is that all resulting records from your query will be fetched from SQL server to the ASP.NET application. If your query returns a million records this will cause some serious performance issues (if you need convincing, try executing such a query in your web application and see the memory consumption of the aspnet_wp.exe in the task manager). That's why a custom paging solution is required where desired behavior is to fetch only the rows from the current page. 

Here I provide some query which provide solution for custom pagination. It helps to improve your search result for your filter data.

Code

TSQL - Decimal to time

 Yesterday I got one query to change numeric value to decimal value. Suddenly I thought "Are datetime and numeric value convertible?" like It convert in excel. Is it possible SQL Server. After 1 hours searching and trial and error ,  I found result.  

The answer is yes. 

Here are the main rules on DATATIME and NUMERIC value conversions:

• During the conversion a DATETIME value will be treated as a NUMERIC value with the number of days relative the base date, Jan 1, 1900 being the integer part, and the time of the day being the decimal part.
• DATETIME values can not be converted to NUMERIC values implicitly using assignment operations.
• NUMERIC values can be converted to DATETIME values implicitly using assignment operations.
• DATETIME values and DATETIME values can be converted to each other explicitly using CAST() or CONVERT() functions.

The tutorial exercise below shows you some good examples:

• Implicit conversion Numeric to Datetime
  
  

  Implicit Conversion Numeric To Datetime

NULLIF (Transact-SQL)

Yesterday I found one more function of sql server. NULLIF which returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF function is available from Sql Server 2005.

Syntax :

NULLIF ( expression , expression )

Example :

The following example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments with budgets that have not changed from the previous year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that receive a budget and to include the budget value from the previous year (use the previous_year value, where the current_year is NULL), combine the NULLIF and COALESCE functions. 

ISNULLIF Example