Today I want to share an SQL Server 2005 stored procedure to make autonumber. This stored procedure provide two numbering templates.
autonumber template #1: ..
autonumber template #2: <1DIGIT-YEAR><1DIGIT-MONTH>.
The Script
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Lucky
-- Create date: October 2008
-- Description: Create autonumber
-- =============================================
CREATE PROCEDURE [dbo].[sp_create_numbering_row]
-- Add the parameters for the stored procedure here
@no VARCHAR(20) = NULL OUTPUT,
@tablename VARCHAR(100),
@numberfieldname VARCHAR(100),
@numberlength INT,
@initialdigit VARCHAR(3),
@templatenumber INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- AUTONUMBERING TEMPLATE #1 ..
-- AUTONUMBERING TEMPLATE #2 <1YEAR><1MONTH>.
DECLARE @sql NVARCHAR(MAX);
DECLARE @result INT;
DECLARE @next INT;
DECLARE @nextnumber NVARCHAR(20);
DECLARE @yearlastdigit VARCHAR(1);
DECLARE @monthdigit NVARCHAR(2);
SET @monthdigit=MONTH(GETDATE())
IF(@monthdigit=10) SET @monthdigit='0'
ELSE IF(@monthdigit=11) SET @monthdigit='A'
ELSE IF(@monthdigit=12) SET @monthdigit='B'
SET @yearlastdigit=(SELECT RIGHT(YEAR(GETDATE()),1))
IF(@templatenumber=1)
SET @sql = 'SELECT @result=CAST(RIGHT('+@numberfieldname+','+CAST(@numberlength as NVARCHAR(2))+') AS INT) FROM ['+@tablename+'] WHERE '+@numberfieldname+' LIKE '''+@initialdigit+'.'+@yearlastdigit+'.%''';
ELSE
SET @sql = 'SELECT @result=CAST(RIGHT('+@numberfieldname+','+CAST(@numberlength as NVARCHAR(2))+') AS INT) FROM ['+@tablename+'] WHERE '+@numberfieldname+' LIKE '''+@yearlastdigit+@monthdigit+'.%''';
EXEC sp_executesql @sql,N'@result INT OUTPUT',@result=@result OUTPUT
IF(ISNULL(@result,0)=0)
SET @next=1
ELSE
SET @next=@result+1
IF(@templatenumber=1)
SET @nextnumber=@initialdigit+'.'+@yearlastdigit+'.'+RIGHT(REPLICATE('0',@numberlength)+CAST(@next AS NVARCHAR(20)),@numberlength)
ELSE
SET @nextnumber=@yearlastdigit+@monthdigit+'.'+RIGHT(REPLICATE('0',@numberlength)+CAST(@next AS NVARCHAR(20)),@numberlength)
PRINT @sql
PRINT @result
PRINT @next
PRINT @nextnumber
SET @sql='INSERT INTO ['+@tablename+'] ('+@numberfieldname+') VALUES ('''+@nextnumber+''')';
PRINT @sql
EXEC sp_executesql @sql
SET @no=@nextnumber
SELECT @no AS no
END
Parameters
| @no | VARCHAR(20) | OUTPUT | Resulting Output |
| @tablename | VARCHAR(100) | INPUT | Name of the table |
| @numberfieldname | VARCHAR(100) | INPUT | Numbering field name |
| @numberlength | INT | INPUT | Number digits length |
| @initialdigit | VARCHAR(3) | INPUT | Initial digit characters |
| @templatenumber | INT | INPUT | Template number: 1 or 2 |
Usage
-- Generate autonumber template #1: ..
exec dbo.sp_create_numbering_row
@tablename='tbl_customer',
@numberfieldname='customer_number',
@numberlength=3,
@initialdigit='CUS',
@templatenumber=1
-- Generate autonumber template #2: <1DIGIT-YEAR><1DIGIT-MONTH>.
exec dbo.sp_create_numbering_row
@tablename='tbl_customer',
@numberfieldname='customer_number',
@numberlength=3,
@initialdigit='',
@templatenumber=2
No related posts.
Tags: autonumber, sql server