Creating an ID or counter

[JustABob]

Is there a way to create a unique ID to return in a rowset that is an integer?

I know there is the newID() function, but it returns something much larger and is non-numeric. What I am basically looking to do is return:

Select JobNumber, Name, StartDate, EndDate From Table

The thing is, this result set does not have a JobNumber column and if possible I'd like to set the first JobNumber = 2001, second to 2002, etc. Or I guess it could be anything random over 20,000 and below 100,000.

Any ideas how to do this? Or if this isnt very clear let me know.

Thanks

 

[DotNetGnat]

something like this:

Select identity(int,2000,1) as JobNumber, Name, StartDate, EndDate From Table

-DNG

 

[gmmastros]

The simplest method is to select your data in to a temp table (that has a identity column). Then select from the temp table back to the client app.

Create Procudure GetDataWithIdentity
As
SET NOCOUNT ON

Create 
Table  #Temp(
       JobNumber Integer Identity([!]2001[/!],1),
       Name VarChar(100), 
       StartDate DateTime,
       EndDate DateTime)

Insert Into #Temp(Name, StartDate, EndDate)
Select Name, StartDate, EndDate
From   Table
Order By (SomeField)

Select JobNumber,
       Name,
       StartDate,
       EndDate
From   #Temp

Drop Table #Temp

The red number specifies the starting number to use. I normally use 1, but you wanted the first record to start at 2001.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

DNG

[!]Server: Msg 177, Level 15, State 1, Line 1[/!]
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetGnat]

George,
Thanks for the tip. will keep that in mind...

-DNG

 

[JustABob]

gmmastros, your solution worked great! I was already moving the data into a table from another table, so I was able to just add that column and things are now working prefectly!

Thanks for your help everyone.