Auto No. Problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Auto No. Problem

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-20 : 06:24:54

Hello,

I have created a table name student_form.


CREATE TABLE [dbo].[studentForm] (
	[form_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
	[first_name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Now I had declared the following function:



CREATE function FormNumber (@id int) 
returns varchar(20) 
as 
begin 
return 'OL09' + right('000' + convert(varchar(10), @id), 10) 
end

alter table student_form add FormNumber as dbo.FormNumber(form_ID)

The result coming for the column FormNumber in the following way:
OL090001
OL090002
OL090003
.
.
.
OL0900010
OL0900011
OL0900012

But I want this in the following way:
OL090001
OL090002
OL090003
.
.
.
OL090010
OL090011
OL090012
.
.
.
OL090123
OL090124
.
.
.
OL093123
OL093124
.
.
.
OL0941231
OL0941231

Please Help!

Daipayan

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-20 : 06:54:04

try like this
DECLARE @refNo VARCHAR(50),
@referencenumber VARCHAR(32)

SELECT @ReferenceNumber = ''

SELECT @ReferenceNumber = MAX(FormNumber)
FROM student_form

PRINT @ReferenceNumber

IF ISNULL(@ReferenceNumber,'') = ''
SELECT @refno = 'OLO9'+ '01'
ELSE
SELECT @refno = 'OLO9'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

SELECT @referencenumber = @refno
SELECT @referencenumber
--and then insert the @referencenumber into that column

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-20 : 07:01:48

Can I define this following T-SQL in function or trigger?

Daipayan

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-20 : 07:03:57

while insertion u can insert the referencenumber into formnumber with the firstname value.....
in insert storedprocedure

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-20 : 07:04:50

go through this link to
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-20 : 07:09:09

Sir,

I'd taken the reference from the following link and created the following function. As per Co. rule, I am not going to declare any procedure, so I have to stick with either trigger or function..

quote:
Originally posted by bklr

go through this link to
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Daipayan

daipayan
Posting Yak Master

181 Posts

Posted - 2009-11-20 : 08:11:47

Sir,

By taking your reference, I declared following trigger:

CREATE TRIGGER Insert_formNumbaer
ON dbo.studentForm
FOR INSERT
AS
SET NOCOUNT ON
update dbo.studentForm
set FormNumber = 
	CASE
		WHEN (select len(form_ID) from studentForm sf where studentForm.form_ID = sf.form_ID) = 1
		THEN ('OLO9'+ RIGHT('000' + CAST(CAST(RIGHT(form_ID, 30) AS INT) AS VARCHAR(6)), 6))
		WHEN (select len(form_ID) from studentForm sf where studentForm.form_ID = sf.form_ID) = 2
		THEN ('OLO9'+ RIGHT('00' + CAST(CAST(RIGHT(form_ID, 30) AS INT) AS VARCHAR(6)), 6))
		WHEN (select len(form_ID) from studentForm sf where studentForm.form_ID = sf.form_ID) = 3
		THEN ('OLO9'+ RIGHT('0' + CAST(CAST(RIGHT(form_ID, 30) AS INT) AS VARCHAR(6)), 6))
		ELSE ('OLO9'+ RIGHT(CAST(CAST(RIGHT(form_ID, 30) AS INT) AS VARCHAR(6)), 6))
	END

and it's working..

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources