SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generated Sequences with SQL Server


Generated Sequences with SQL Server

Author
Message
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59348 Visits: 17947
sasansamani (11/28/2012)
Sean Lange (11/28/2012)
sasansamani (11/28/2012)
Our department wants to automatically generate permit numbers for our customers when they apply for a license.They want something similar to a key generator. Yes SQL Server is my only resource at this moment.


I can help but you have to help me understand the scope of what you are trying to do. Is there a certain format you need this to be in? Is there any reason these permit numbers can't be numeric?


This is the format they want 00000-V/C 0000- AT- YR

They want to be able distinguish if it’s a vehicle or container. They want to be able distinguish if it’s a temporary or permanent. It can’t be a numeric permit number.


Honestly I am trying to help but you just aren't giving me any details. What is that format? Keep in mind I can't see your screen and I have no knowledge of your tables or your project.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59348 Visits: 17947
sasansamani (11/28/2012)
Sean Lange (11/28/2012)
sasansamani (11/28/2012)
Our department wants to automatically generate permit numbers for our customers when they apply for a license.They want something similar to a key generator. Yes SQL Server is my only resource at this moment.


I can help but you have to help me understand the scope of what you are trying to do. Is there a certain format you need this to be in? Is there any reason these permit numbers can't be numeric?


This is the format they want 00000-V/C 0000- AT- YR

They want to be able distinguish if it’s a vehicle or container. They want to be able distinguish if it’s a temporary or permanent. It can’t be a numeric permit number.


I am going to take a shot in the dark as to what you want. It seems that most your "key" is made up of values already in the table. This is where you could use a computed column. It also seems from earlier that you have some poor decisions in your table structure. It seems like you have material and classification as a single field. This is what is known as attribute splitting. Each column should contain one and only one value. Don't make things harder for yourself. Also you want to store the year. I would not do this as an int column. Instead make it datetime and then you can return only the year portion if you want.

Here is my take on how you could do this. You may have to


IF OBJECT_ID('TempDB..#Haulers','U') IS NOT NULL
drop table #Haulers

create table #Haulers
(
HaulerID int identity primary key,
Asset char(1),
Material char(1),
Classification char(1),
DateCreated datetime not null default getdate(),
PermitNumber as right(replicate('0', 5) + cast(HaulerID as varchar(5)),5) + '-' + Asset + Material + Classification + cast(year(DateCreated) as char(4)) PERSISTED
)

insert #Haulers
select 'A', 'M', 'C', GETDATE() union all
select 'T', 'U', 'X', GETDATE()

select * from #Haulers



Please notice the PERSISTED keyword at the end of the column definition. This is important so you can add an index to this column. You can read more about persisted columns here. http://msdn.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sasansamani
sasansamani
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 66
thank you very much. I realized using SQL managment studio to generate these permit numbers is a lame idea. They can just use Microsoft Access form to generate these alpha numeric permits. They pick the value and then it generates the alpha numeric permits.

Thank you everyone.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search