SQL Server: Custom Sorting

This is one of the many custom sorting we do with SQL queries. In few cases we may need to sort all records of a result set in a particular order except one specific row containing some specific value, which we may want to keep on the top of all other rows or we may like to move it to the bottom. Now, let’s relate this to few real time scenarios.

Scenario-1:

Sometimes we need an element to be placed at bottom in the list. For example, a Dropdownlist containing educational qualifications may contain  an item with text as “Other”. While showing all these educational qualifications from database we may need this particular element (i.e, “Other”) to move to the bottom in the list. If we try to sort this list in the SELECT query by normal ORDER BY keyword (like ORDER BY Qualification) then we may not get the desired sequence.

To get the desired result we need to use CASE WHEN keyword along with ORDER BY. Let’s illustrate this with following example-

Simple ORDER BY :

SELECT * FROM
(
 SELECT 'I.Sc.' AS Qualification
 UNION
 SELECT 'B.Sc.' AS Qualification
 UNION
 SELECT 'Other' AS Qualification
 UNION
 SELECT 'P.G.' AS Qualification
 UNION
 SELECT 'MCA' AS Qualification
 UNION
 SELECT 'DCA' AS Qualification
 UNION
 SELECT 'P.H.D' AS Qualification
) AS TBL
ORDER BY Qualification

OUTPUT:

 

 

 

 

ORDER BY with CASE WHEN :

SELECT * FROM
(
 SELECT 'I.Sc.' AS Qualification
 UNION
 SELECT 'B.Sc.' AS Qualification
 UNION
 SELECT 'Other' AS Qualification
 UNION
 SELECT 'P.G.' AS Qualification
 UNION
 SELECT 'MCA' AS Qualification
 UNION
 SELECT 'DCA' AS Qualification
 UNION
 SELECT 'P.H.D' AS Qualification
) AS TBL
ORDER BY CASE WHEN Qualification='Other' THEN 'ZZZZZ' ELSE Qualification END

OUTPUT:

 

 

 

 

 

EXPLAINATION:

Here we have set the value of item “Other” as “ZZZZZ” while the sorting is being done. Generally “ZZZZZ” will be the last item in any real world list and hence will move to the last.

Scenario 2:

In some other cases we may need the desired item to be moved to top of the result set. An example for this situation can be, a Dropdownlist containing items for Book Category. The list may have an item with text “General” , which is meant for those books whose category is not clearly known. Let’s assume that most of the books are supposed to belong this category. Then we have to place this item in the first index of the Dropdownlist so that it will be easier for user to pick the item. Now, we need a little modification in the ORDER BY clause as follows.

ORDER BY CASE WHEN Qualification='General' THEN NULL ELSE Qualification END

 

I’ll love to hear from you.

Thanks

 

You may also like :

Dynamic ORDER BY in SQL Server

ASP.Net : DropDownList containing category – sub category hierarchy

Introduction:

This article will provide us a way to embed both categories and sub-categories hierarchy in a single DropDownList exactly same as the image on the left.

I searched the web for this but did not find exactly what I needed. So, I planned to write my own logic for this. If somebody has a better idea then I really appreciate and request to share.

Description:

Lets take an example where we need to show states and districts of India in a single DropDownList with their hierarchy. Here state will behave as category and districts can be considered as sub-categories.

In DB part we need two tables i.e, States and Districts may be like following structure.

• State       –> (StateId int pk, StateName varchar(50))
• Districts –> (DistrictId int pk, StateId int fk, DistrictName varchar(50))

Or, this script may help you creating the tables

CREATE TABLE [dbo].[States](
 [StateId] [int] IDENTITY(1,1) NOT NULL,
 [StateName] [varchar](50) NULL,
 CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
 [StateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Districts](
 [DistrictId] [int] IDENTITY(1,1) NOT NULL,
 [StateId] [int] NOT NULL,
 [DistrictName] [varchar](50) NULL,
 CONSTRAINT [PK_Districts] PRIMARY KEY CLUSTERED
(
 [DistrictId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Districts] WITH CHECK ADD CONSTRAINT [FK_Districts_States1] FOREIGN KEY([StateId])
REFERENCES [dbo].[States] ([StateId])
GO

ALTER TABLE [dbo].[Districts] CHECK CONSTRAINT [FK_Districts_States1]
GO

Now insert some sample data or you can execute this script.

/****** Object: Table [dbo].[States] ******/
SET IDENTITY_INSERT [dbo].[States] ON
INSERT [dbo].[States] ([StateId], [StateName]) VALUES (1, N'Odisha')
INSERT [dbo].[States] ([StateId], [StateName]) VALUES (2, N'West Bengal')
INSERT [dbo].[States] ([StateId], [StateName]) VALUES (3, N'Bihar')
INSERT [dbo].[States] ([StateId], [StateName]) VALUES (4, N'Jharkhand')
SET IDENTITY_INSERT [dbo].[States] OFF
/****** Object: Table [dbo].[Districts] ******/
SET IDENTITY_INSERT [dbo].[Districts] ON
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (1, 1, N'Mayurbhanj')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (2, 1, N'Keonjhar')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (3, 1, N'Khorda')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (4, 1, N'Balasore')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (5, 2, N'Hoogly')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (6, 2, N'Howrah')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (7, 2, N'Jalpaiguri')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (8, 3, N'Aurangabad')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (9, 3, N'Patna')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (10, 4, N'Bokaro')
SET IDENTITY_INSERT [dbo].[Districts] OFF

Place a DropDownList in your page.

<asp:DropDownList ID="drpDistricts" runat="server"></asp:DropDownList>

Lastly do the coding required to bind the DropDownList. Things to remember are we have a single DropDownList and it should be meant for choosing a sub-category(District), which means categories should be disabled for clicking. Although there may be several ways like looping the sub-categories and inserting categories in-between etc. but, I personally prefer to query both of them in a single query.

SELECT '- '+DistrictName AS TextField,DistrictId AS ValueField,StateId FROM DBO.Districts UNION SELECT UPPER(StateName),0,StateId FROM dbo.States ORDER BY StateId,DistrictId

Put DataTextField =”TextField” and DataValueField=”ValueField”

Now, lets do the coding to disable the categories (States).

foreach (ListItem item in drpDistricts.Items)
 {
 if (item.Value.ToString() == "0")
 {
 item.Attributes.Add("Style", "color:maroon");
 item.Attributes.Add("Disabled", "true");
 }
 }

That’s all.
Thanks for reading.
Feedback or suggestion are most welcome.

SQL Server: Delete a record with condition on multiple columns of another table

If you want to delete a record from a table where the relation with another table is on multiple columns or you want to check it for some conditions on multiple columns of another table then with a simple join your job can be done.

Example:

DELETE Table1

FROM  Table1

INNER JOIN  Table2 ON  Table1.Column1=Table2.Column1 AND Table1.Column2=Table2.Column2

Drop a comment in case you have any question or suggestion.

Thanks !

SQL Server: Delete/Update duplicate records

Problem:

My third article in Problem-Solution approach to keep it short and simple.

When you insert two records with same values for each of the columns in a table and then if you want to delete one of those two records then you may face problems like if you open the table data in SSMS and try to delete one of those rows by right clicking then it will not work. If you try to delete with a simple delete query then both the duplicate records will be deleted. You will face the same problem while updating those records also. Lets examine it using a simple example.

Example:

CREATE TABLE Demo (Id INT,Name VARCHAR(20))

INSERT INTO Demo VALUES(1,'TEST1')

INSERT INTO Demo VALUES(2,'TEST2')

INSERT INTO Demo VALUES(3,'TEST3')

INSERT INTO Demo VALUES(3,'TEST3')

Now, try to delete/update one of the record having Id=3.

The query for Update may be like-

UPDATE Demo SET Name='3Test' WHERE Id=3

The query for Delete may be like-

DELETE Demo WHERE Id=3

Solution:

Solution for Update

SET ROWCOUNT 1
UPDATE Demo SET Name='3Test' WHERE Id=3
SET ROWCOUNT 0

Solution for Delete

SET ROWCOUNT 1
DELETE FROM Demo WHERE Id=3
SET ROWCOUNT 0

Analysis:

According to MSDN , SET ROWCOUNT Causes SQL Server to stop processing the query after the specified number of rows are returned.  So, the above query will affect only one record and this is what we needed.

[+] ADDED LATER [+]
[Thanks Manas Ranjan Dash for bringing this to notice]
As the MSDN link says “Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server”, lets try some other alternative solutions.
Solution 2:
Using TOP –>

DELETE TOP (1) FROM Demo WHERE Id=3 AND Name='Test3'

This query will delete 1 record and you can change it as required.
Solution 3:
Using Common Table Expression & Ranking Functions –>

;WITH CTE(Id,Name,SlNo)
AS
(
	SELECT Id,Name,ROW_NUMBER() OVER(ORDER BY Id) AS SlNo
	FROM Demo
)
DELETE FROM CTE WHERE SlNo > 1 AND Id=3 AND Name='Test3'

The above script will leave only a single record with Id=3 and Name=’Test3′.

Hopefully this article may be useful to you. Please put your comments and suggestions.

Thanks !!!

SQL Server: Stored Procedure for update with optional parameters

Problem:

One more article in Problem-Solution approach to keep it short and simple.

When we are writing a stored procedure to update different fields of a table for one or more records, we may write as the below script.

CREATE PROCEDURE [dbo].[UpdtEmployee]
(
@EmpId INT,
@EmpName VARCHAR(100)=NULL,
@Address VARCHAR(200)=NULL,
@EmpPhotoPath VARCHAR(100)=NULL,
@MobileNo VARCHAR(14)=NULL
)
AS
BEGIN
UPDATE dbo.Employee
SET EmpName=@EmpName,Address=@Address,EmpPhotoPath=@EmpPhotoPath,MobileNo=@MobileNo
WHERE EmpId=@EmpId
END

The problem with the above stored procedure is when we want to update only those fields/columns of the record for which corresponding parameter is not having NULL values then it will not yield the anticipated result. In this scenario this stored procedure will update all the fields with the values from corresponding parameters. Let’s take an example so that the problem can be understood clearly.

Example: Let’s assume that we have inserted values to all the fields of a record. Now we want to modify only the mobile number of that employee. If we call this procedure then it will make values of all other fields to NULL as we have declared the default value for all the parameters as NULL.

Solution:

Solution to this problem is to avoid updating those fields which have corresponding parameters with NULL values or, we can update existing data corresponding to those fields. We will go for the later one. Now, the modified script may look like

CREATE PROCEDURE [dbo].[UpdtEmployee]
(
 @EmpId INT,
 @EmpName VARCHAR(100)=NULL,
 @Address VARCHAR(200)=NULL,
 @EmpPhotoPath VARCHAR(100)=NULL,
 @MobileNo VARCHAR(14)=NULL
)
AS
BEGINd
 UPDATE dbo.Employee
 SET EmpName=ISNULL(@EmpName,EmpName),Address=ISNULL(@Address,Address),EmpPhotoPath=ISNULL(@EmpPhotoPath,EmpPhotoPath),MobileNo=ISNULL(@MobileNo,MobileNo)
 WHERE EmpId=@EmpId
END

Hopefully this article may be useful to you.

Thanks !!!

SQL Server: An optimal way to create procedure with multiple optional parameters

Problem:

I took the Problem-Solution approach to write this post to keep it short and simple.

Here the problem I am talking about is the scenario when you have to write a stored procedure in SQL Server with multiple search options/parameters then you may use some IF…ELSE .. statements to do it. Let’s take an short example.

Example:

CREATE PROCEDURE [dbo].[getEmployeeList]
(
 @DeptId INT=NULL,
 @OfficeId INT=NULL
)
AS
BEGIN
 IF(@DeptId IS NOT NULL AND @OfficeId IS NOT NULL)
 BEGIN
 SELECT *
 FROM dbo.Employees
 WHERE DeptId=@DeptId AND OfficeId=@OfficeId
 END
 ELSE IF(@DeptId IS NOT NULL AND @OfficeId IS NULL)
 BEGIN
 SELECT *
 FROM dbo.Employees
 WHERE DeptId=@DeptId
 END
 ELSE IF(@DeptId IS NULL AND @OfficeId IS NOT NULL)
 BEGIN
 SELECT *
 FROM dbo.Employees
 WHERE OfficeId=@OfficeId
 END
 ELSE
 BEGIN
 SELECT *
 FROM dbo.Employees
 END
END

Here we can have 4 combinations of search options.

Now, if we want add some more parameters like SectionId,DesignationId,CityId etc. to this stored procedure then it will be a tough task to manage it and may end-up with a 100′s lines of script.

Solution:

I found a solution for this problem by creating multiple conditions for a single WHERE… tag. Now, if I implement it to the previous example then the stored procedure will be like the below script-

CREATE PROCEDURE [dbo].[getEmployeeList]
(
 @DeptId INT=NULL,
 @OfficeId INT=NULL
)
AS
BEGIN
 SELECT *
 FROM dbo.Employees
 WHERE (@DeptId IS NULL OR DeptId=@DeptId)
 AND (@OfficeId IS NULL OR OfficeId=@OfficeId)
END

Just add conditions like these two to the WHERE part, as many as optional parameters you have in your stored procedure.

I hope it will be helpful to those who are looking a solution for the above said problem. Please share your thoughts and ideas regarding this post.

Thank you.

SQL Server: encrypt password

This post is about encrypting a value before inserting it to your password  field in SQL Server. This is very much similar as PASSWORD(),SHA1(), etc. in MySQL. In SQL Server we can use the same through PWDENCRYPT() function.

Syntax for insert:

INSERT INTO Table1 (field1,field2) VALUES(value1, PWDENCRYPT(value2))

Example:

INSERT INTO dbo.UserLogin (Uid,Pwd) VALUES('User', PWDENCRYPT('Suvendu'))

Here, ‘Suvendu’ is the value for my Pwd field which I want to encrypt.

Syntax for comparing:

SELECT * FROM Table1 WHERE field1=value1 AND field2=PWDENCRYPT(value2)

Example:

SELECT * FROM dbo.UserLogin WHERE Uid='User' AND Pwd=PWDENCRYPT('Suvendu')

Reverse  is not possible, I mean decryption can not done for this encrypted value.

NOTE: It is a undocumented function. As far I know, PWDENCRYPT() is supported by SQL Server version upto SQL Server 2012 (beta) but, MSDN says it may not be available there in the future versions of SQL Server.

SQL Server: Function to get previous session year

When you need to get previous session year of a supplied session year then you can use the below function.

Step-1 :

Execute below script.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Suvendu>
-- Create date: <13 Mar 2012>
-- Description: <returns prev sess yr of the supplied sess yr>
-- =============================================
CREATE FUNCTION [dbo].[fuGetPrevSessionYr]
(
 @SessYr NVARCHAR(7)
)
RETURNS NVARCHAR(7)
AS
BEGIN
 DECLARE @PrevSessYr NVARCHAR(7)
 SELECT @PrevSessYr=CAST((CAST(LEFT(@SessYr,4) AS INT)-1) AS VARCHAR(4))+'-'+RIGHT(LEFT(@SessYr,4),2)

 RETURN @PrevSessYr
END

Step-2:

Syntax for calling the function-

SELECT dbo.fuGetPrevSessionYr(<SessionYr>) [FROM TABLE1]

Where  <SessionYr> is the session year, whose previous session you want to show.

SQL Server: Function to get fiscal year or, session year

You can use this function to get the fiscal/financial year from a supplied date value. This may be useful when you have a datetime column in a table but you want to show it’s fiscal year. Another case when you may need this function is you have a datetime column in your table but you have to show the transaction made, specified fiscal year/session year wise or when you are preparing any statistical report.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Suvendu>
-- Create date: <10 mar 2012>
-- Description: <returns the fiscal year of the supplied date>
-- =============================================
CREATE FUNCTION [dbo].[fuGetFiscalYear]
(
 @SuppliedDate DATETIME
)
RETURNS NVARCHAR(7)
AS
BEGIN
 DECLARE @SessYr NVARCHAR(7)
 SELECT @SessYr= CASE WHEN DATEPART(MONTH,@SuppliedDate)>3 THEN CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate)+1 AS VARCHAR(4)),2) ELSE CAST(DATEPART(YEAR,@SuppliedDate)-1 AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4)),2) END
 RETURN @SessYr
END
GO

[Note: I have assumed the fiscal year as Indian fiscal year or, financial year. You can change it accordingly or, can use the below function]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Suvendu>
-- Create date: <10 mar 2012>
-- Description: <returns the fiscal year of the supplied date>
-- =============================================
CREATE FUNCTION [dbo].[fuGetSessionYear]
(
 @SuppliedDate DATETIME,
 @StartMonth INT
)
RETURNS NVARCHAR(7)
AS
BEGIN
 DECLARE @SessYr NVARCHAR(7)
 SELECT @SessYr= CASE WHEN DATEPART(MONTH,@SuppliedDate)>@StartMonth THEN CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate)+1 AS VARCHAR(4)),2) ELSE CAST(DATEPART(YEAR,@SuppliedDate)-1 AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4)),2) END
 RETURN @SessYr
END
GO

Difference between DELETE and TRUNCATE in SQL Server

I found many people confused between delete and truncate in SQL Server when there is a need to delete all the records from a table. In this quick post I have tried to compile the difference between them. Have a look…

DELETE (Without Where condition)	TRUNCATE
1.Delete without where condition or any restriction deletes all the rows from a table but one at a time, so it deletes records through a  loop for all the rows.	1.Truncate deallocates data pages from the table, thus deleting all the rows at once. It doesn’t go for any loop and thus faster than delete.
2.Delete is DML (Handles table data)	2.Truncate is DDL (Handles Table)
3.Delete can be rollback.	3.Truncate can’t be rollback using log file, once the transaction is complete.
Explanation: Delete writes data to log file before committing but, Truncate doesn’t. Delete removes one row at a time and deleted row is stored in transaction log but, Truncate deallocates the data pages in a table and stores only this deallocation in the transaction log.
4.On Delete all delete triggers will be fired.	4.No trigger will be fired upon Truncate command.
5.Delete is slower as compared to Truncate.	5.Truncate is faster than Delete
6. Delete (without condition) doesn’t reset the identity column value, if present.	6. Truncate command resets the value to initial value or seed for identity column.
7. Delete command acquires table and/or page and row locks for the whole table.	7.Truncate acquires only table and page locks for the table. Since no row lock are used less memory is required.
8. Delete can leave empty pages in table since as page removal requires a table lock which doesn’t necessarily happen.	8.Truncate removes all pages. No empty pages are left behind in a table.

I hope this will help you …

Please comment if you have any question or, suggestion.

Thanks !!!