Tuesday, April 17, 2007

Automatically insert SortOrder value in SQL

So, the other day I ran into an interesting problem. I've been using CodeSmith and .netTiers to develop all that "boring business/data access code" for an application at work. One of our requirements was to have a list of things with a user-specified sort order.

So, I added a SortOrder column to the table (not sort_order, but more on that later), and ran my code-gen. I didn't want to put the "automatic sort order insertion" into a stored procedure (since they're all generated), but I also didn't want to have to hit the database several times just to figure out what the next value should be.

Then someone showed me that you can have a function for a default value in a column in SQL Server. I knew this instinctively (NEWID() for a uniqueidentifier, or GETDATE() for a "date inserted" column) but I hadn't thought about using my own function.

So, here it is:

DECLARE @newMax int

SELECT @newMax = MAX(SortOrder) + 1 FROM TableWithSortColumn WHERE SomeForeignKeyId = (SELECT SomeForiegnKeyId FROM TableWithSortColumn WHERE Id = (SELECT IDENT_CURRENT('TableWithSortColumn')))

RETURN @newMax

I just created the function, set my default value for the sort column to dbo.GetNewSortOrder() and

Disclaimer: I'm not particularly sure this is really the right place to do this type of thing. If anyone has any suggestions or comments, please let me know. If you think this is a terrible idea and will cause my application to start fires and kidnap small children - definitely let me know. I'd like to avoid any legal entanglements.


Achutha Krishnan said...

hi, your idea is good. And I have a question now. Consider I have a table with 2 fields.

1. CountryID
2. CountryName

From front-end, I'm going to insert only the country name. Now is it possible to assign CountryID automatically? I mean whenever I enter a CountryName, the CountryID should be assigned to that and when ever I remove the CountryName, the CountryID should also be removed and be assigned to another CountryName. Is that possible?

Achutha Krishnan said...

Or give me an idea to do like that!

Nic Webb said...

Well - you could make a CountryID as an Identity/PK column in SQL and let SQL handle that for you.

Try this CREATE TABLE (using Microsoft SQL)

CREATE TABLE [dbo].[Country](
[CountryID] [bigint] IDENTITY(1,1) NOT NULL,
[CountryName] [varchar](50) NOT NULL

I wouldn't recommend trying to re-assign a CountryID to a new name - especially if you have any other tables referencing the CountryID column as a foreign key. If someone wants to remove a country, just remove whole row or add another bit column to your table denoting the "enabled/disabled" status of that particular country.

Achutha Krishnan said...

that was great. Now I'll try as you said. Actually I'm going to implement this for two table which contains Categories and SubCategories. I've posted one topic related to this in asp.net forums. You can check here http://forums.asp.net/t/1199737.aspx if you wish. Thanks a lot for the help!