Next sequence number? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Next sequence number?

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-04 : 17:00:48

[code]I have a function below to generating the next sequence number but it keeps generating duplicate values.
Can you see what's wrong with the function below?. Or do you suggested a better way to generating the next
sequence number.

Much appreciate.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Loan]') AND type in (N'U'))
DROP TABLE [dbo].[Loan]
GO

CREATE TABLE [dbo].[Loan]
(
[LoanNum] [varchar](10) NOT NULL,
[OrderId] [int] NOT NULL,
[CustomerLoanNumber] [varchar](50) NULL

CONSTRAINT [PK_Loan] PRIMARY KEY CLUSTERED
(
[LoanNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT Loan ( LoanNum, OrderId, CustomerLoanNumber)
VALUES ( '100000', 1, '1000027061'),
( '100001', 2, '223190885');
GO

SELECT *
FROM Loan;
go

LoanNum OrderId CustomerLoanNumber
---------- ----------- -------------------
100000 1 1000027061
100001 2 223190885

DROP FUNCTION dbo.fnGenNextLoanNum;
go

CREATE Function dbo.fnGenNextLoanNum
(
@LoanNum VARCHAR(10)
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @NextSeqLoanNum VARCHAR(10)

SET @NextSeqLoanNum = ( SELECT (COALESCE(CAST(MAX(LoanNum) AS INT), 0) + 1 )
FROM Loan
)

RETURN @NextSeqLoanNum
END
GO

SELECT dbo.fnGenNextLoanNum(LoanNum) AS 'NextSeqLoanNum'
FROM Loan ;
GO

-- Problem...

NextSeqLoanNum
--------------
100002
100002

--Should return next sequence number.

NextSeqLoanNum
--------------
100002[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-04 : 17:03:28

Why aren't you using an identity value?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-04 : 17:10:42

Because my boss wants to make it consistence with other tables in others DBs. They defined as VARCHAR(10) instead Identity. We will
used the LoanNum to do lots of calculations and comparison in other DBs. I wish I can change to Identity datatype, that would make life a lot easier.

Thanks.

quote:
Originally posted by tkizer

Why aren't you using an identity value?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 17:36:23

[code]SELECT dbo.fnGenNextLoanNum(LoanNum) AS 'NextSeqLoanNum'
~~FROM Loan ;~~
[/code]
is the next number-to-use, no?

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 17:40:08

Beware that:


CAST(MAX(LoanNum) AS INT)

will find the ALPHANUMERICALLY largest value, and then convert THAT to int - e.g.:



SELECT CAST(MAX(LoanNum) AS INT)
FROM
(    SELECT '9' AS LoanNum UNION ALL
     SELECT '10' AS LoanNum
) AS X

it will be fine IF your LoanNum width is fixed, OR you pad it using leading zeros / right-align it


SELECT MAX(CAST(LoanNum AS INT))

will work - but will be VERY slow if used repeatedly on a large table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources