RESERVE_CLAIM_NUMBER_SQL_TEMPLATE = "SELECT getnextclaimnumber() as claim_id";

DECLARE @RetVal int

EXECUTE @RetVal = [dbo].[getnextclaimnumber] 

ALTER PROCEDURE [claims].[getnextclaimnumber]
 @NextCMSClaimNumber VarChar(20) OUTPUT
AS
BEGIN
 SET NOCOUNT ON

 
  -- Calls next avaliable claim number in sequence --
  SELECT @NextCMSClaimNumber  = NEXT VALUE FOR claims.ClaimNumber
 
  
  -- Appends '0' to claim number --
  Select '0'+ @NextCMSClaimNumber 
 
 End

declare @result varchar(20)
exec [claims].[getnextclaimnumber] @NextCMSClaimNumber = @result OUTPUT

select @result as [ResultOfGetNextClaimNumber]

create table #test 
       (ClaimNumber_ident int identity(1,1) 
       , ClaimNumber as ('0' + convert(varchar(20), [ClaimNumber_ident])) 
       , otherCols int)

insert #test (otherCols) values (10),(11),(12)
select * from #test

OUTPUT:

ClaimNumber_ident ClaimNumber           otherCols
----------------- --------------------- -----------
1                 01                    10
2                 02                    11
3                 03                    12

quote:

---

Originally posted by TG

this is how you get the value of an output variable:

declare @result varchar(20)
exec [claims].[getnextclaimnumber] @NextCMSClaimNumber = @result OUTPUT

select @result as [ResultOfGetNextClaimNumber]

If you're new to Sql Server then you may not be aware of IDENTITY columns. They are a built in sequence object by table. Not sure why you are converting the sequence to varchar and prepending a '0' but if that is necessary for some reason you could add a computed column based on the identity value. Something like this:

create table #test 
       (ClaimNumber_ident int identity(1,1) 
       , ClaimNumber as ('0' + convert(varchar(20), [ClaimNumber_ident])) 
       , otherCols int)

insert #test (otherCols) values (10),(11),(12)
select * from #test

OUTPUT:

ClaimNumber_ident ClaimNumber           otherCols
----------------- --------------------- -----------
1                 01                    10
2                 02                    11
3                 03                    12

EDIT:
you could also leave the value as integer and perform that conversion only when you display the value.

One peculiarity of sequence objects is that when sql server service restarts for any reason (like a server reboot) the next value jumps by whatever the cache value is. You can configure the cache size or set it to [no cache] but there will be a performance hit and disk io impact. This behavior doesn't happen with identity columns.

Be One with the Optimizer
TG

---

quote:

---

Originally posted by TG

>> Can't pick where I want the identity value to start
yes you can.
Either when the identity column is created or via a DBCC CheckIdent RESEED

>>Although how would I prepend a 0 to a int?
You can't. You must convert the number to a character type data type in order to prepend '0'.
This applies to CONCAT() as well. Only works on strings.

Be One with the Optimizer
TG

---