Auto Increment Key Example



CREATE TABLE [dbo].[complaint]
(
[complaint_ID] [nvarchar](50) NOT NULL, CONSTRAINT [PK_complaint] PRIMARY KEY CLUSTERED([complaint_ID]ASC)
)


/****** Object: StoredProcedure [dbo].[autokeygeneration] Script Date: 10/12/2011 17:49:18 ******/

--select isnull(complaint_ID,null) from complaint

--exec autokeygeneration '09','Jan','03'

--select * from complaint

--print substring('09Feb020001',6,2)

CREATE procedure [dbo].[autokeygeneration]
(
@year VARCHAR(5),
@month VARCHAR(8),
@date VARCHAR(3)
)
AS
BEGIN
	DECLARE @callno VARCHAR(50)
	IF((SELECT COUNT(ISNULL(complaint_ID,null)) FROM complaint WHERE
		SUBSTRING(complaint_ID,1,2)=@year and
		SUBSTRING(complaint_ID,3,3)=@month and
		SUBSTRING(complaint_ID,6,2)=@date)=0)
		BEGIN
			SET @callno=@year+@month+@date+'0001'
		END
	
	ELSE
	BEGIN	
		SELECT 	
		@callno=@year+@month+@date+REPLICATE(0,4-LEN(MAX(right(complaint_ID,4))+1))+CONVERT(VARCHAR,max(right(complaint_ID,4))+1)
		FROM complaint
		WHERE SUBSTRING(complaint_ID,1,2)=@year AND SUBSTRING(complaint_ID,3,3)=@month and SUBSTRING(complaint_ID,6,2)=@date
		INSERT INTO complaint (complaint_id) VALUES (@callno)
		SELECT @callno
	END
END
Advertisements

About Gokul Dahal, Nepal

Software Engineer, Freelancer, Design and Develop web applications,Software Developer Nepal, FreeLancer Nepal Contact : gokuldahal@gmail.com
This entry was posted in SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s