SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Generate Serial No in SQL server?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nickjack
Starting Member

34 Posts

Posted - 10/13/2008 :  05:46:06  Show Profile  Reply with Quote
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

Sweden
29908 Posts

Posted - 10/13/2008 :  05:47:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/13/2008 :  05:48:41  Show Profile  Reply with Quote
create an identity column id and just use

'S' + right('0000' + cast(id as varchar(5)),4)
as the surveyid value
Go to Top of Page

nickjack
Starting Member

34 Posts

Posted - 10/13/2008 :  06:00:39  Show Profile  Reply with Quote
Hi
I read this column but what after C9999 will it increment further
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 10/13/2008 :  06:03:49  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 10/13/2008 :  06:08:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

nickjack
Starting Member

34 Posts

Posted - 10/13/2008 :  06:18:08  Show Profile  Reply with Quote
If i say Next Number S10000, S10001 like this...
how to achive this.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 10/13/2008 :  06:19:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

nickjack
Starting Member

34 Posts

Posted - 10/13/2008 :  06:30:49  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/13/2008 :  06:36:06  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 10/13/2008 :  06:38:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

nickjack
Starting Member

34 Posts

Posted - 10/13/2008 :  06:46:34  Show Profile  Reply with Quote
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.

Edited by - nickjack on 10/13/2008 06:48:45
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/13/2008 :  06:58:59  Show Profile  Reply with Quote
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.
Go to Top of Page

nickjack
Starting Member

34 Posts

Posted - 10/13/2008 :  07:38:33  Show Profile  Reply with Quote
And Say If i Don't need ID only SurveyId in database Get Increment Then?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/13/2008 :  08:03:49  Show Profile  Reply with Quote
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.
Go to Top of Page

nickjack
Starting Member

34 Posts

Posted - 10/14/2008 :  03:08:35  Show Profile  Reply with Quote
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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 10/14/2008 :  03:15:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

nickjack
Starting Member

34 Posts

Posted - 11/07/2008 :  03:06:22  Show Profile  Reply with Quote
Hi,


can we create function inside a Procedure
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/07/2008 :  03:09:22  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000