SQL Server: generating a number for a column

Here is the situation that I am currently struggling with.

I have two tables: Pro1 , Pro2 (a reference table)

Pro1: ID int, ProviderID1 varchar(25)

Pro2: ID int, ProviderID2 varchar(25)

Here is the thing. Both ProviderIDs (1,2) are always ABCxxxxxxxxxxxx (total 15) with ABC prefix. pro2 is pre-populated with other prefixes as well but we are not interested in that.

Now I need to populate pro1.ProviderID according to:

• check in pro2 maximum value where ProviderID2 like 'ABC%'
• now for providerID1 add 1 to that existing max providerid2
• if not found in pro2, then generate new providerid1 with prefix abc- "ABC000000000100"
• keep adding 1 for next providerid1.

Example:

lets say in pro2, MAX(providerID2) found is ABC000000000111

so in pro1, providerid1 it should generate ABC000000000112 and ABC000000000113 for next ID1 and so on.

If there is NO providerID2 in pro2 where providerid2 like 'ABC%' then generate ABC000000000100 , ABC000000000101 for next and so on.

Please note that providerid ALWAYS has to be ABC followed by 12 integers (total 15).

Thanks a Lot !! Here's what I am using:

IF  (SELECT count(providerid2) FROM pro2 WHERE providerid2 like 'ABC%')>0

   BEGIN

     IF object_id('tempdb..#te') is not null
     DROP TABLE #te
     CREATE TABLE #te (id int,pid int,pid2 varchar(15),providerid1 varchar(20))
     INSERT INTO #te (id)
     SELECT id FROM pro1  

     DECLARE @oldID int
     SET @oldID =  (SELECT right (MAX(providerID2),12) FROM pro2 WHERE providerid2 like 'ABC%')
     UPDATE #te
     SET pID = new
     FROM ( SELECT pid,new =   
            ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) + @oldID end
            FROM #te)  #te

     DECLARE @pid TABLE (pid int)
     INSERT INTO @pid (pid) SELECT pid FROM #te

     UPDATE #te
     SET pid2 = (SELECT stuff(replicate('0',12),12 - len(pid) +1,12,pid))FROM #te
     DECLARE @pid2 TABLE (pid2 int)
     INSERT INTO @pid2 (pid2) SELECT pid2 FROM #te 
     UPDATE #te
     SET providerID1 = 'ABC' + CONVERT(VARCHAR(15), pid2, 15) FROM #te

     UPDATE pro1
     SET providerID1 = #te.providerid1
     FROM pro1
     INNER JOIN #te on #te.id = pro1.ID


   END

ELSE


     IF object_id('tempdb..#tempr') is not null
     DROP TABLE #tempr
     CREATE TABLE #tempr (id int,pid int,pid2 varchar(15), providerID1 varchar(15))
     INSERT INTO #tempr (id)
     SELECT id FROM pro1

     UPDATE #tempr
     SET pID = new
     FROM ( SELECT pid,new = 
            ROW_NUMBER() OVER(PARTITION BY ID  ORDER BY ID) + 99 end
            FROM #tempr)  #tempr                   

     DECLARE @pi TABLE (pid int)
     INSERT INTO @pi (pid) SELECT pid FROM #tempr
     UPDATE #tempr
     SET pid2 = (SELECT stuff开发者_如何学JAVA(replicate('0',12),12 - len(pid) +1,12,pid)) FROM #tempr

     DECLARE @pi2 TABLE (pid2 int)
     INSERT INTO @pi2 (pid2) SELECT pid2 FROM #tempr 
     UPDATE #tempr
     SET providerID1 = 'ABC' + CONVERT(VARCHAR(15), pid2, 15) FROM #tempr 

     UPDATE pro1
     SET providerID1 = #tempr.providerid1
     FROM pro1
     INNER JOIN #tempr on #tempr.id = pro1.ID