Generate Serial No in SQL server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Generate Serial No in SQL server?

Author

Topic

nickjack
Starting Member

34 Posts

Posted - 2008-10-13 : 05:46:06

AS Id is auto incremented i want a column say Survey Id also increment?
Like S0001
S9999+ 1............
and so on.with respect to ID.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 05:47:47

See http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 05:48:41

create an identity column id and just use

'S' + right('0000' + cast(id as varchar(5)),4)
as the surveyid value

nickjack
Starting Member

34 Posts

Posted - 2008-10-13 : 06:00:39

Hi
I read this column but what after C9999 will it increment further

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-13 : 06:03:49

nickjack: please stop poting duplicate topics. it's annoying and will only lead to confusion.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 06:08:50

quote:
Originally posted by nickjack

I read this column but what after C9999 will it increment further

You will have to make a businees decision what should happen when maximum has been reached.
Should next number after S9999 be T0001?
Should next number after S9999 be S10000?

E 12°55'05.63"
N 56°04'39.26"

nickjack
Starting Member

34 Posts

Posted - 2008-10-13 : 06:18:08

If i say Next Number S10000, S10001 like this...
how to achive this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 06:19:34

Make a calculated column and have the expression as

'S' + CAST(identityColumnNameHere AS VARCHAR(12))

E 12°55'05.63"
N 56°04'39.26"

nickjack
Starting Member

34 Posts

Posted - 2008-10-13 : 06:30:49

create function SurveyID (@id int)
returns char(5)
as
begin
return 'S' + right('0000' + convert(varchar(10), @id), 4)
end

Can U Modify It For Me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 06:36:06

quote:
Originally posted by nickjack

create function SurveyID (@id int)
returns char(5)
as
begin
return 'S' + right('0000' + convert(varchar(10), @id), 4)
end

Can U Modify It For Me.

no need of function . just define a calculated column as Peso suggested.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 06:38:55

[code]CREATE FUNCTION dbo.fnSurveyID
(
@id INT
)
RETURNS VARCHAR(12)
AS
BEGIN
RETURN CASE
WHEN @id < 10000 THEN 'S' + REPLACE(STR(@id, 4), ' ', '0')
ELSE 'S' + CAST(@id AS VARCHAR(12))
END
END[/code]

E 12°55'05.63"
N 56°04'39.26"

nickjack
Starting Member

34 Posts

Posted - 2008-10-13 : 06:46:34

Hi
i am new to SqlServer i am achieving above means S0001 to S9999 Like this
create table SMaster
(
ID int identity not null primary key,
)

create function SurveyID (@id int)
returns char(5)
as
begin
return 'S' + right('0000' + convert(varchar(10), @id), 4)
end

alter table smaster add surveyID as dbo.SurveyID(ID)

What i need it Keep on Incrementing as i explain above. Do i need to change my code? Or Better way to achieve my requirments.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 06:58:59

quote:
Originally posted by nickjack

Hi
i am new to SqlServer i am achieving above means S0001 to S9999 Like this
create table SMaster
(
ID int identity not null primary key,
)

create function SurveyID (@id int)
returns char(5)
as
begin
return 'S' + right('0000' + convert(varchar(10), @id), 4)
end

alter table smaster add surveyID as dbo.SurveyID(ID)

What i need it Keep on Incrementing as i explain above. Do i need to change my code? Or Better way to achieve my requirments.

since id is autoincrementing, you dont manually need to autoincrement values. on each record insertion, ID will get next value automatically and surveyID will get corresponding value as its calaculated column based on id.

nickjack
Starting Member

34 Posts

Posted - 2008-10-13 : 07:38:33

And Say If i Don't need ID only SurveyId in database Get Increment Then?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 08:03:49

quote:
Originally posted by nickjack

And Say If i Don't need ID only SurveyId in database Get Increment Then?

then you need to insert the records into a temporary table with id column, get generated id value and append it to series to generate surveyid value.

nickjack
Starting Member

34 Posts

Posted - 2008-10-14 : 03:08:35

And one thing more if i use Id and SurveyId both with respect to each other then two from(say form1.aspx and form2.aspx) subbmit at one time and will the record successfully inserted?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 03:15:30

Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

nickjack
Starting Member

34 Posts

Posted - 2008-11-07 : 03:06:22

Hi,

can we create function inside a Procedure

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 03:09:22

do you mean creating or invoking function inside procedure? I think you meant invoking a function from inside the procedure. If yes, its possible. If you want former, i wanted ask what's purpose behind doing this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources