Generating numbers in SQL

hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem?. Are there any samples/codes available for this.

Any help would be highly appreciated.

thanks,
-sriramWhat are you doing this for? Uniqueness for records in a table? You can use an Identity Column for that...what is the business problem you are trying to solve?|||thanks for your reply.

My client wants to do it in the way i explained before. Is there a way??

-sriram|||You could do it a few ways, the incrementing number being the point of divergence. To get the current month you simply use the Month() function with GetDate()

ex:
SELECT Month(GetDate())

There are a number of ways to build the second part...

One would be to create an identity column and after the insert read the SCOPE_IDENTITY(), combine it with the Month(GetDate()) to get your number. The problem with this is that it relies on work after the insert which could be done through a stored proc or a trigger.

Another approach would be to select the top value from the column, trimming the month prefix and the creating your value by incrementing that number.

Do you need to stuff with zeros? Does the month need to be 1 or 01; does the second number need to be led by zeros?|||hi,

thanks for replying.

The second number needs to be led with zeros.

thanks,

-sriram|||You can use the SQL-Server Function Replicate() for this:

DECLARE @.MaxLength AS int
SET @.MaxLength = 6
DECLARE @.INT AS int
SET @.INT = 1
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 000001
SET @.INT = 1234
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 001234

|||Here is some code that will help; I tried not to use UDFs because I dont know what version of SQL you are using. Regardless this will show you how to parse the old value out and generate a new one.

-- Assumes that the fixed length is 10

Declare @.OldValuechar(10)
Set@.OldValue= '0100000365'

Declare @.OldNumberint
Set@.OldNumber= Cast(Substring(@.OldValue,3, LEN(@.OldValue)-2) as int)

Declare @.MonthNamevarchar(10)
Set@.MonthName= DateName(M, Cast(Left(@.OldValue,2) + '/01/2000' as datetime))

-- Show the old values
select @.OldValue
select@.OldNumber
select @.MonthName

Declare @.MonthPrefixchar(2)
Set@.MonthPrefix= (Select Case WHEN Month(GetDate()) >= 10 THEN Cast(Month(GetDate()) as char(2)) ELSE '0' + Cast(Month(GetDate()) as char(1)) END)

Declare @.NewNumbervarchar(8)
Set@.NewNumber= Cast(@.OldNumber + 1 as varchar)

Declare @.Prefix varchar(7)
Set@.Prefix= ''

While Len(@.Prefix) < (7 - len(@.NewNumber))
Set @.Prefix = '0' + @.Prefix

Declare @.NewValuechar(10)
Set@.NewValue= @.MonthPrefix + @.Prefix + Cast(@.NewNumber as char)

-- Show the New Value
Select @.NewNumber
Select@.NewValue|||thanks,

i think it makes sense.

it should work..

anyway thanks for your reply.

-sriramsql

generate table scripts

Hi

I'm developing a website using SQL Server Express 2005 and VIsual Web Developer Express, and now want to start testing my website in a live environment. Until now I have been manually creating tables on my web database to match those on my local DB, but I have so many tables I don't want to manually recreate them all again. Is there a method in SQL Server Express 2005 that will allow me to generate insert scripts for all the tables?

Cheers

Andy

This should help

http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx

|||

Download the SQL Server Database Publishing Wizard:

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Jos

|||

Blimey! That was quick. Thanks for the prompt responses, guys!

Generate scripts only for stored procedures and views?

Hello, I have two environments, testing and developing, sometimes on developing environment I make chagnes to views or stored procedures, the first time I copied the whole database, but when the tester began to test the second cycle I made the same and he losed the changes, the tables wont change, but the views and sps may change, so I need an easy way to change those objects without having to copy all the database?

I tried but the generate scrips task but I didnt see an option to script only views and procedures.

What version of SQL Server are you using? I'm using SQL Server 2005 Express, and when I go through the script wizard to script objects, I see the "Choose Object Types you want to script." just fine.

In Object Explorer, right-click on your database -> Tasks -> Generate Scripts...

|||I am using sql 2005 developer edition and I dont see that option anywhere.|||what option don't you see where and at what point?|||Did you complete the wizard? I remember that option near the end of the wizard.|||You were right, it was in the last step. I really would think to put it in the first step. Its just my opinion but thanks.