Complex SQL query

Hi Experts,

I need to generate a dynamic custom number based on definition column defined for each customer.
Example
Definition Table: 
Definition column    Running value                  CLIENTID
@@R##!R               05 - 10; 20 -100 * 10;              1
RRRRR!                1;2;30-100000*3;999-1;0;            2

Open in new window

@represent Alpha
# represent number
! represent special character
R represent pre-define number
Running Table:
CLIENTID  RunningValue
    1          ab0511@20 
    1          ab0611@30
    2          12309990&
    2          23609981&

Open in new window

Second Value for client 1 is ab0611@30 because ,
3rd and fourth character should get auto incremented by 1 from the previous value for corresponding client, till it reaches 10. Once its reaches 10 it should errorout
8th & 9th Character should be incremented by 10 from the previous value runnning table. Till it reaches 100. Once its reaches 100 it should error out.

Second Value for client 2 is 23609971& because ,

First character is auto incremented from previous value ,therefore,it makes it as 2.There is no end limit for first character.
Second character is auto incremented from previous value,therefore,it makes it as  3.There is no end limit for first character.
Third and fourth character is multiply by 3 from previous value,therefore,it makes it as 60. This has limit upto 100000.
fifth,sixth and seventh character is auto decremented from previous value, therefore,it makes it as 998.
eight character is auto incremented from previous value ,therefore,it makes it as 1
ninth character is special character so, it makes it as &

definition column and running value length are not static, they are differ from client to client.
SQL GuruAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
I'll look at it when I have a free weekend around the Christmas. Do you accept CLR Stored Procedures and/or CLR user-defined functions?
SQL GuruAuthor Commented:
I prefer using user-defined functions or stored procedure using query.
PortletPaulEE Topic AdvisorCommented:
where is the question?
or are you expecting one of us to do the entire thing for you?
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

SQL GuruAuthor Commented:
Hi Experts,

I have developed the solution.However, i don't feel my query is optimize.It's working as expected. But is there anyway to optimize further on this query.
DECLARE @CustomerId INT
	,@Current_value VARCHAR(50)
	,@Format_value VARCHAR(50)
	,@Dynamic_Value VARCHAR(50)
	,@ColumnNameList VARCHAR(MAX) --output
	,@RowCnt INT 
	,@cv_CNT INT
	,@fv_CNT INT
	,@TotalRowCount INT 
	,@LAST_R_Pos INT 
	,@Is_R VARCHAR(50)
	,@len int
	,@DECREMENT INT = 0 
	,@ASCII INT
	,@C CHAR(1)
	,@LAST_R INT 
	
SET @CustomerId = 1
SET @Current_value = 'fFfeD-1-34-zcaz-@-00100'--REMOVE FIRST AND LAST DELIMITER 
SET @Format_value = '@@@@@-R-##-@@@@-!-RRRRR'
SET @Dynamic_Value = '1:9*1;00100:99999*9999999'  --REMOVE LAST DELIMITER; 

CREATE TABLE #Format_value (
	ID INT
	,Format_Position int 
	,Format_Length INT
	,Format_Grouping VARCHAR(100))

CREATE TABLE #Current_value (
	ID INT
	,CV_Position int 
	,CV_Length INT
	,CV_Grouping VARCHAR(100))

CREATE TABLE #Dynamic_Value ( 
     ID INT  IDENTITY (1,1)
	,Item VARCHAR(8000)
	,startingNumber VARCHAR(50)
	, EndingNumber VARCHAR(50)
	, symbols VARCHAR(50)
	,incrementalNumber VARCHAR(50)
	,StartingNumber_Length int
	)


CREATE TABLE #Combine (
   ID INT
   ,Item VARCHAR(50)
	,startingNumber VARCHAR(50)
	, EndingNumber VARCHAR(50)
	, symbols VARCHAR(50)
	,incrementalNumber VARCHAR(50)
	,StartingNumber_Length INT
	,rn INT
	,CV_Position int 
	,CV_Length INT
	,CV_Grouping VARCHAR(50)
	,Format_Position INT 
	,Format_Length INT
	,Format_Grouping VARCHAR(50)
	)


	CREATE TABLE  #Complete_Table (ID INT, 
			CV_Position INT,
			CV_Length INT,
			CV_Grouping VARCHAR(50),
			Format_Position INT,
			Format_Length INT,
			Format_Grouping VARCHAR(50),
			Item VARCHAR(50),
			startingNumber VARCHAR(50),
			EndingNumber VARCHAR(50),
			symbols VARCHAR(50),
			incrementalNumber VARCHAR(50),
			StartingNumber_Length INT,
			Next_Value VARCHAR(50),
			is_Reset BIT
			 ) 
	INSERT INTO #Format_value
	SELECT * FROM [dbo].[SplitWords](@Format_value)

	INSERT INTO #Current_value
	SELECT * FROM [dbo].[SplitWords](@Current_value)

	INSERT INTO #Dynamic_Value (Item,startingNumber,EndingNumber,symbols,incrementalNumber,StartingNumber_Length)
	SELECT * FROM [dbo].[Split]((@Dynamic_Value),';')


	SELECT @RowCnt=ISNULL (COUNT(CV.ID) ,0)
	 FROM #Current_value cv 
			INNER JOIN #Format_value fv 
			on cv.ID = fv.ID
			and cv.CV_Length <> fv.Format_Length 
	
	SELECT @cv_CNT = COUNT(ID) FROM #Current_value cv 
	SELECT @fv_CNT =  COUNT(ID) FROM #Format_value

	IF( @RowCnt <> 0 )  OR (@cv_CNT <> @fv_CNT)
		 SELECT 'IF Current Value Length IS NOT EQUAL TO Format Value Length THEN THROW an Error'
	ELSE 
	    
	
   INSERT INTO #Combine( ID,Item,startingNumber, EndingNumber, symbols,incrementalNumber,StartingNumber_Length,rn,CV_Position,CV_Length,CV_Grouping,Format_Position ,Format_Length,Format_Grouping )
	
	

	SELECT ID,Item, startingNumber, EndingNumber, symbols,incrementalNumber,StartingNumber_Length,rn,CV_Position,CV_Length,CV_Grouping,Format_Position ,Format_Length,Format_Grouping 
	FROM #Dynamic_Value dv
	FULL OUTER JOIN  (
	
	SELECT ROW_NUMBER() OVER (ORDER BY CV.ID) as rn, 
			cv.CV_Position,
			cv.CV_Length,
			cv.CV_Grouping,
			fv.Format_Position,
			fv.Format_Length,
			fv.Format_Grouping
		   FROM #Current_value cv 
				INNER  JOIN #Format_value fv 
				ON cv.ID = fv.ID
				where fv.Format_Grouping like '%R%'
				
			 )running_Value_details
			ON dv.ID = running_Value_details.rn
			

INSERT INTO #Complete_Table(CV.ID, cv.CV_Position,cv.CV_Length,cv.CV_Grouping,fv.Format_Position,fv.Format_Length,fv.Format_Grouping,c.Item,c.startingNumber,c.EndingNumber,
c.symbols,c.incrementalNumber,c.StartingNumber_Length)
        select CV.ID, 
			cv.CV_Position,
			cv.CV_Length,
			cv.CV_Grouping,
			fv.Format_Position,
			fv.Format_Length,
			fv.Format_Grouping,
			c.Item,
			c.startingNumber,
			c.EndingNumber,
			c.symbols,
			c.incrementalNumber,
			c.StartingNumber_Length
			
		   FROM #Current_value cv 
				INNER  JOIN #Format_value fv 
				ON cv.ID = fv.ID 
				LEFT JOIN #Combine C
				ON CV.CV_Position= C.CV_Position
		--  WHERE FV.Format_Grouping LIKE '%R%'

		--SELECT @TotalRowCount =COUNT (ID) FROM #Complete_Table 
		SELECT TOP 1 @LAST_R_Pos =id,@Is_R =Format_Grouping FROM #Complete_Table WHERE Format_Grouping LIKE '%R%' ORDER BY 1 DESC 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGINING:
--Increments starts

--SELECT @LAST_R_Pos , @RowCnt AS ROWS 
WHILE (@LAST_R_Pos >= 1  and @RowCnt >= 0 )


  BEGIN
        IF (@Is_R like '%R%' ) -- Checking whether it's a running value 
		BEGIN 
		    SELECT @Is_R= Format_Grouping -- calcluating the length of new value with format type 
		 
		      FROM ( 

			    SELECT  id ,CASE WHEN LEN(NV) <= LEN( Format_Grouping) --appending the nextvalue to bit
					  THEN  CONVERT (VARCHAR(50),RIGHT( POWER(10, LEN(Format_Grouping))+ NV ,LEN(Format_Grouping)))

					   ELSE  CONVERT (VARCHAR(50),NV)

					END AS NEXT_VALUE,NV as NV,LEN(Format_Grouping ) FG	,CV_Position	,Format_Grouping	
			FROM 
				(		
				SELECT id , (convert(int,CV_Grouping) + convert (int,incrementalNumber)) AS NV,-- Calculate the value --NEED TO WRITE CASE STATEMENT FOR MULTIPLE ARTHIMETIC OPEARTION 
				Format_Grouping,CV_Length,CV_Position FROM #Complete_Table WHERE ID =@LAST_R_Pos and Format_Grouping like'%R%'
				)a )  b 
				where LEN(NEXT_VALUE) <> FG 
				
				-- CHECKING THE NEXT VALUE HAS OVERFLOW OR NOT 
				--SELECT @Is_R

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
				IF (@@ROWCOUNT > 0 AND @Is_R LIKE'%R%') 
				   BEGIN 
							
							 UPDATE #Complete_Table 
							 SET Next_Value =  CONVERT (VARCHAR(50),RIGHT( POWER(10, LEN(Format_Grouping))+ 0 ,LEN(Format_Grouping)))  --Re-Set the Value to 0
							 WHERE ID =@LAST_R_Pos

						
							
							 SELECT TOP 1 @LAST_R_Pos = id ,@Is_R = Format_Grouping FROM #Complete_Table WHERE  
							 (Format_Grouping NOT LIKE '%!%' AND Format_Grouping NOT LIKE'%#%' )  --Exclude the constant position
							  AND  ID < @LAST_R_Pos 
							 ORDER BY 1 DESC 

--select * from #Complete_Table
									   GOTO BEGINING
								
					 END

			   ELSE 
					  
					 BEGIN 
						 UPDATE #Complete_Table 
							 SET Next_Value =  CONVERT (VARCHAR(50),RIGHT( POWER(10, LEN(Format_Grouping)) +
							 (convert(int,CV_Grouping) + convert (int,incrementalNumber)) ,LEN(Format_Grouping))) --INCREMENT THE VALUE
							 WHERE ID =@LAST_R_Pos
							 SELECT * FROM #Complete_Table

							 	 SELECT @LAST_R = COUNT(1) FROM #Complete_Table WHERE  
							 (Format_Grouping  LIKE '%R%'  )  --Exclude the constant position
							  AND  ID <@LAST_R_Pos ORDER BY 1 DESC 


							  
							 IF( @LAST_R ) > 0 
							 BEGIN 
									 SELECT TOP 1 @LAST_R_Pos = id ,@Is_R = Format_Grouping FROM #Complete_Table WHERE  
							 (Format_Grouping  LIKE '%R%'  )  --Exclude the constant position
							  AND  ID <@LAST_R_Pos ORDER BY 1 DESC 

									GOTO BEGINING

							END 
							  ELSE 
							        GOTO NOFURTHERR
						 END
					
							
					 END

					 ELSE 
					    BEGIN
						-- SELECT 'APLHA'
						
						

							  SELECT @len = len(CV_Grouping ) 
									from #Complete_Table where ID = @LAST_R_Pos
									--select @len as len 

						WHILE (@LEN > 0 ) 
						BEGIN 
			--SELECT  (reverse( substring ( reverse (CV_Grouping ) , 1 ,@DECREMENT + 1) ))as original_character ,@DECREMENT,--CHAR (ASCII (
			--char(ascii (reverse (substring ( reverse (CV_Grouping ) , 1 ,@DECREMENT + 1)))+1) as next_character
			----) +1 )
			--from #Complete_Table where id =@LAST_R_Pos 
			SET @ASCII =''
					SELECT @ASCII  = ascii (reverse (substring ( reverse (CV_Grouping ) , 1 ,@DECREMENT + 1)))+1
						from #Complete_Table where id =@LAST_R_Pos 
						 IF (@ASCII >= 64 AND @ASCII <= 91) OR ( @ASCII >= 96 AND @ASCII <= 123) --OR (@ASCII > 47 AND @ASCII < 58)
							BEGIN 
								SELECT @C =CHAR(@ASCII) -- DO THE INCREMENT  AND EXIT 
								--SELECT @C AS CHARS,@ASCII


								IF ( @ASCII = 123 ) 
								    BEGIN  
									--select next_value from #Complete_Table 

									  UPDATE #Complete_Table 
										SET Next_Value =( CASE WHEN Next_Value IS NULL   AND @C  LIKE '%{%' THEN 'a'
										                       WHEN Next_Value IS NOT NULL   AND @C  LIKE '%{%'THEN 'a'+  Next_Value  
														  ELSE 
														  @C +  Next_Value   END ) 


										WHERE ID =@LAST_R_Pos

										--SELECT CASE WHEN Next_Value IS NULL   AND @C  LIKE '%{%' THEN 'C'
										--                       -- WHEN  Next_Value IS NOT NULL  AND @C  LIKE '%{%'   THEN  Next_Value + 'C' 
										--				  ELSE 
										--				  Next_Value END AS AFTERUPD   FROM #Complete_Table WHERE ID =@LAST_R_Pos 

										 SET @DECREMENT = @DECREMENT +1 --find the next character 
										SET @LEN = @LEN -1 
									END

								ELSE IF ( @ASCII = 96) 
								BEGIN 
										UPDATE #Complete_Table 
										SET Next_Value =isnull(Next_Value, '') + replace (CV_Grouping,reverse ( substring ( reverse (CV_Grouping ) , 1 ,@DECREMENT + 1) ), '6')
										WHERE ID =@LAST_R_Pos
										 SET @DECREMENT = @DECREMENT +1 --find the next character 
										SET @LEN = @LEN -1 
								END 
								ELSE IF (@ASCII = 91)

								BEGIN 
								   	UPDATE #Complete_Table 
										SET Next_Value =isnull(Next_Value, '') +  replace (CV_Grouping,reverse ( substring ( reverse (CV_Grouping ) , 1 ,@DECREMENT + 1) ), '1')
										WHERE ID =@LAST_R_Pos
										 SET @DECREMENT = @DECREMENT +1 --find the next character 
										SET @LEN = @LEN -1 
								END 

								ELSE IF (@ASCII = 64 )
								BEGIN 
								UPDATE #Complete_Table 
										SET Next_Value =isnull(Next_Value, '') +  replace (CV_Grouping,reverse ( substring ( reverse (CV_Grouping ) , 1 ,@DECREMENT + 1) ), '4')
										WHERE ID =@LAST_R_Pos
										 SET @DECREMENT = @DECREMENT +1 --find the next character 
										SET @LEN = @LEN -1 
								END 
								ELSE 
								BEGIN 
								--select * from #Complete_Table
								UPDATE #Complete_Table 
										SET Next_Value = (CASE WHEN Next_Value IS NULL THEN @c  
																  WHEN Next_Value IS NOT NULL THEN @c + Next_Value
																ELSE @C +  Next_Value END ) 

											WHERE ID =@LAST_R_Pos
								
								--select * from #Complete_Table
								IF (@DECREMENT >= 0 ) 
								BEGIN 

								UPDATE #Complete_Table 
										SET Next_Value = substring (CV_Grouping,1,len(CV_Grouping) - len(Next_Value)) + Next_Value 
											WHERE ID =@LAST_R_Pos
							
							--select * from #Complete_Table

								END 

								
									goto OUTOFLOOP
								END 
							
							
							 
							 

								
							END
				       -- select '@DECREMENT +1 '
				       
			END

---------------------------------------------------------------------------------------------------------------------			  
		OUTOFLOOP:
		                     SET @DECREMENT = 0 
							 IF @LAST_R_Pos = 1 
							SET @LAST_R_Pos = 0 SET @Is_R =''  --TO exit last element 

							SELECT
							 TOP 1 @LAST_R_Pos = id ,@Is_R = Format_Grouping FROM #Complete_Table WHERE  
							 (Format_Grouping NOT LIKE '%!%' AND Format_Grouping NOT LIKE'%#%' )  --Exclude the constant position
							  AND  ID <  @LAST_R_Pos ORDER BY 1 DESC 

--------------------------------------------------------------------------------------------------------------------------------

							 GOTO BEGINING
						END
		SET @LAST_R_Pos = @LAST_R_Pos -6 
  END
	 
	 

			NOFURTHERR: 
			


 SELECT @ColumnNameList  = CASE WHEN Next_Value IS NULL 
								THEN COALESCE(@ColumnNameList +'-','') + CV_Grouping 
								ELSE COALESCE(@ColumnNameList +'-','') + Next_Value END
                     FROM 
                       #Complete_Table
					       order by id 

					   select @ColumnNameList

	DROP TABLE #Dynamic_Value
	DROP TABLE #Format_value
	DROP TABLE #Current_value
	DROP TABLE #Combine
	DROP table #Complete_Table
	

Open in new window

pcelbaCommented:
Your solution is not optimal due to the tool used... SQL Server should not be usurped this way because your task has almost nothing to do with the core SQL Server functionality...

BUT it is OK if the number of calls is low so they don't affect the overall speed. If you are talking about thousands of calls per hour then additional optimization is highly recommended.

Much more important is your specs which you should review ...

The spec is talking about characters but samples show digits which is something different.
The spec also says "There is no end limit for first character". Where it goes from "9"? To "10"?  And how do we recognize what is the second "character" then?

Also unclear is: "Third and fourth character is multiply by 3 from previous value,therefore,it makes it as 60. This has limit upto 100000."

I can see 30 and 60 in your sample. Where is it "multiply by 3 from previous value"? 30 * 3 = 90. Etc. etc.

So your code maybe works but I cannot confirm it at all.
SQL GuruAuthor Commented:
The spec also says "There is no end limit for first character". Where it goes from "9"? To "10"?  And how do we recognize what is the second "character" then?

The pattern is already designed, if it reaches 10 then , the corresponding position is reset back to 0. And next left most character or number gets incremented.
e.g.
aaa-9   1:;
even if the running number doesn't have end value, it re-set as below
aab-0
Also unclear is: "Third and fourth character is multiply by 3 from previous value,therefore,it makes it as 60. This has limit upto 100000."

3 and 4 is number constant.
e.g.
SET @Current_value = 'fFfeD-1-34-zcaz-@-00100'--REMOVE FIRST AND LAST DELIMITER
SET @Format_value = '@@@@@-R-##-@@@@-!-RRRRR'
SET @Dynamic_Value = '1:9 * 1;00100:99999*9

dynamic value matches corresponding R in the format value.
so,
R - 1:9 increment by 1
RRRRR - 00100:99999 increment by 9
So next value is
fFfeD-2-34-zcaz-@-00109
pcelbaCommented:
It is better but still not clear... You should realize it is hard to code something which is defined in your brain only...

And your specs are still ambiguous, e.g.:

aaa-9   1:;
is reset to:
aab-0

How can you recognize  "aaa" changes to "aab" ?

Another example:
SET @Dynamic_Value = '1:9 * 1;00100:99999*9
vs.
SET @Dynamic_Value = '1:9*1;00100:99999*9999999'

What is the increment for 00100:99999*9999999 ?  For me the increment seems to be higher than the max value in the range. Sounds like a nonsense...

But you are saying the solution works for you so it means you know more than you disclosed here...
SQL GuruAuthor Commented:
1. I have a condition in a code to check if number gets reset to zero.If yes, then take the next character and increment the number by definition/ ascii value by 1.  If it's successfully gets incremented then exist the loop else iterate to next character.

2.  '1:9*1;00100:99999*9999999' ->This is a typo mistake.Please take below one as example.
 e.g    '1:9 * 1;00100:99999*9
   a. '1:9 * 1; this means starting 1 to ending 9 increment by 1 delimiter by ";"
   b. 00100:99999*9  this means 00100 to 99999 increment by 9.

High level Explaination:
 Program should work like speedometer in vehicle. speedometer has only numeric value where here it contain alpha-numeric.

Detailed Explaination :
Each customer has an unique custom format. And they need to generate next series of alpha-numeric string.

Example:
Format type : @@-R-##-!-RRR
Running value: 1:;100-999*100
First Customer Value  : 'ab-1-34-@-100'

Symbol representation:

Format type :
@ - Alpha
R – Running Value
# - Numbers constant
! – Special Characters constant

Running value:
 :    - delimiter for starting and ending number. Where ending is optional.
;     - delimiter for multiple running values for a particular customer
(+,-,*,/)  –  Any arithmetic operation can be performed on the running value.


First Customer Value :
•      First value for any particular customer should be given by the customer.
•      It should be delimited with ‘-‘ and should follow the same pattern defined in the format type .  
•      E.g. 'ab-1-34-@-100'.
From here on, system will do auto calculation for next series of numbers.

So, Next number will be ab-2-34-@-200
Value got incremented from 1 - 2 & 100 - 200

Once it reaches ab-9-34-@-900
Since number reached the maximum, 9 & 900 gets reset to 0 and increment next alpha which will be c
i.e ac-0-34-@-000
Once it reaches az-9-34-@-900
Since alphabet reached the maximum, z is reset to a and increment next alpha which will be b.
i.e. ba-0-34-@-000
It continue until it reaches the maximum possible combination.
i.e.zz-9-34-@-900
Once it's reaches maximum combination.It should error out.
Note this format is not static, format will differ from customer to customer.
pcelbaCommented:
It is getting better! (Just the speedometer in vehicle does not reset its value :-)

You should define how to recognize whether to increment the alphabetic string left to the number or not.  Does the format '@@@@@-R-##-@@@@-!-RRRRR' say "increment the string left to the first running value BUT don't increment the string left to the second running value because there is ! character"?

And also what are increment values for the string having mixed case, e.g. "fFfeD". Here I can imagine E follows D but what should follow after Z? And what about alphanumeric strings? Are they allowed?
SQL GuruAuthor Commented:
Just the speedometer in vehicle does not reset its value :-
It does bro , Last digit goes from 0 to 9 to make 1 km and again it starts from 0.

 Does the format '@@@@@-R-##-@@@@-!-RRRRR' say "increment the string left to the first running value BUT don't increment the string left to the second running value because there is ! character"?

I don't understand.

And also what are increment values for the string having mixed case, e.g. "fFfeD". Here I can imagine E follows D but what should follow after Z? And what about alphanumeric strings? Are they allowed?

Z will be re-set back to Capital 'A'. It's follows the ASCII format. All Capital letters will be incremented with the capital letter.
Small letter will be incremented with small letter.
e.g. fFfeD if this gets incremented then it could be like fFfeE , so on till zZzzZ.
pcelbaCommented:
Let say we have following three formats:

'@@@@@-R-##-@@@@-!-RRRRR'
'@@@@@-R-##-@@@@-RRRRR'
'@@@@@-R-##-RRRRR'

What happens when the RRRRR is reset? What @ value is incremented?
SQL GuruAuthor Commented:
'@@@@@-R-##-@@@@-!-RRRRR'
@@@@@-R-##-@@@INCREMENT-!-00000
Condition: INCREMENTED @ is not equal z/Z. In case of z/Z then it iterate to the next character and reset z/Z to a/A.

'@@@@@-R-##-@@@@-RRRRR'
@@@@@-R-##-@@@INCREMENT-00000
Condition: INCREMENTED @ is not equal z/Z. In case of z/Z then it iterate to the next character and reset z/Z to a/A.

'@@@@@-R-##-RRRRR'
'@@@@@-INCREMENT-##-00000
INCREMENT R and exit.
SQL GuruAuthor Commented:
Hi pcelba,

Did I able to clear your doubts ?
pcelbaCommented:
Yes, it seems the spec is complete now. You just may see how difficult it is to write it with all necessary details...

And now to the question of the optimization necessity. How often is the code called? Does it block some other processing? Or does it consume too much SQL Server resources? If not then don't optimize it.
SQL GuruAuthor Commented:
Yes, still it's in designing phrase. But i have managed to develop algorithm where it meets my purpose.

If customer wants to generate next 100000 number then , it's creates bottleneck. for 100000 it's take around 35 minutes and request are processed one by one to maintain the consistency in the number.
So , if there are 10 request in a queue, then all other 9 users has to wait till the previous users completes his/her transaction.

Considering the worst scenario,  if all 10 users wants to generate next 100000 number then 10th users has to wait approx.  5 hours to process his request.

So, I feel optimizing the query will boost up the performance.
pcelbaCommented:
Yes it should be optimized. The easiest optimization seems to be to generate your custom numbers in advance (over night) and store them into a separate table.
Scott PletcherSenior DBACommented:
Your current table is fine for entering/confirming formulas.  But I would add another table for internal use that "pre-analyzes" the pattern and stores the separate parts of the formula so that you don't have to re-compute it every time.  You could add INSERT and UPDATE triggers to the table to do that part.

You also have contradictions in what you've posted.

For example:
"30-100000*3 ... is multiplied by 3";
"b. 00100:99999*9  this means 00100 to 99999 increment by 9."
Only in the first case does "*" seem to mean multiply: everywhere else it's an addition (increment).
Which also contradicts this:
"(+,-,*,/)  –  Any arithmetic operation can be performed on the running value."
But '*' means the same as '+'??

Even more difficult is the use of RRRRR first meaning five different values, and then later to mean 1 output value with 5 digits max.  
"RRRRR!                1;2;30-100000*3;999-1;0;" vs
"SET @Format_value = '@@@@@-R-##-@@@@-!-RRRRR'
SET @Dynamic_Value = '1:9 * 1;00100:99999*9"

I submit those uses are incompatible and too contradictory to use.  The first method is more logical and consistent.  Thus, you need a separate method to assign a fixed length.  I think it really ought to be in the Running_Value definition itself, rather than in the pattern, because that is more consistent with other limits begin there, such as the max value.  Therefore maybe:

SET @Dynamic_Value = '1:9[1] * 1;00100:99999[5]*9"  OR
SET @Dynamic_Value = '1:9(1) * 1;00100:99999(5)*9"

Of course  you could also do this:
SET @Format_value = '@@@@@-R[1]-##-@@@@-!-R[5]'  OR
SET @Format_value = '@@@@@-R(1)-##-@@@@-!-R(5)'  OR

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.