How to maintain two digit values in sp?

Question

How to maintain two digit values in sp?

Charmer

SSChampion

Points: 14903
More actions
March 15, 2012 at 10:43 am

#252632

Hi friends,
IF OBJECT_ID('Sp_Coded') IS NOT NULL BEGIN Drop procedure Sp_Coded End
go
CREATE PROCEDURE [dbo].[Sp_Coded] (@ParentTableName varchar(50), @ColumnName varchar(40), @CodeID int, @Number bigint, @MaxLength int, @SetID varchar(10), @Flag varchar(10), @SrcTablename varchar(100), @SrcCodeValueColumn varchar(100), @SrcDescColumn varchar(100), @SQL varchar(5000)) AS
BEGIN
DECLARE @MySql varchar(5000)
IF ( @Flag = '2' )
BEGIN
SET @MySql = 'SELECT Distinct 0 as PnxJurisID , ' + Cast(@CodeID as Varchar(15)) + ' as CodedID, B.' + Cast(@SrcCodeValueColumn as varchar(50)) + ' as SrcCodeValueRef, B.'+ CAST( @SrcCodeValueColumn as varchar(50)) + ' as SrcCodeValue, B.'+ CAST( @SrcDescColumn as varchar(50)) +' as SrcCodeDescr ' +
' From dbo.' + @ParentTableName + ' A left outer join SrcCodeMap on A.' + @ColumnName + ' = SrcCodeMap.SrcCodeValueRef and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(15)) +
' left outer join '+ @SrcTablename +' B on B.'+ @SrcCodeValueColumn +' = A.' + @ColumnName + ' WHERE SrcCodeMap.JurisID is Null AND A.' + @ColumnName + ' Is Not Null '
END
else IF ( @Flag = '1' )
BEGIN
SET @MySql = 'SELECT Distinct 0 as PnxJurisID , ' + Cast(@CodeID as Varchar(15)) + ' as CodedID, ' + @ParentTableName+ '.' + @ColumnName + ' as SrcCodeValueRef, CASE WHEN SCTABE.TECOD# IS NULL THEN LTRIM(RTRIM(' + @ParentTableName+ '.' + @ColumnName + ')) ELSE LTRIM(RTRIM(SCTABE.TECOD#)) END as SrcCodeValue, SCTABE.TEDESC as SrcCodeDescr ' +
' From dbo.' + @ParentTableName + ' left outer join SrcCodeMap on ' + @ParentTableName + '.' + @ColumnName + ' = SrcCodeMap.SrcCodeValueRef and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(15)) +
' left outer join SCTABE on rtrim(ltrim(SCTABE.TECOD#)) = rtrim(ltrim('+ @ParentTableName+ '.' + @ColumnName + ')) and SCTABE.TEMST# = ' + Cast( @SetID as Varchar(8))
+ ' WHERE SrcCodeMap.JurisID is Null '
END
else IF ( @Flag = '3' )
BEGIN
SET @MySql = @SQL
END
print @MySql
IF OBJECT_ID('tempSrcCodeDB', 'U') IS NOT NULL
DROP TABLE tempSrcCodeDB;
CREATE TABLE tempSrcCodeDB (JurisID int, CodedID varchar(15), SrcCodeValueRef Varchar(50), SrcCodeValue varchar(60), SrcCodeDescr varchar(60))
INSERT INTO tempSrcCodeDB (JurisID,CodedID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr)
EXEC(@MySql)
DECLARE @JurisID int, @CodedID varchar(15), @SrcCodeValueRef varchar(50), @SrcCodeDescr varchar(60), @SrcCodeValue varchar(40), @DecNumber bigint
SET @DecNumber = @Number
DECLARE MySql CURSOR FOR SELECT * FROM tempSrcCodeDB
OPEN Mysql
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( (LEN(@SrcCodeValue) <= @MaxLength) and ((@SrcCodeValueRef) <> '') and ((@SrcCodeValueRef) <> null) )
BEGIN
insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr, @SrcCodeValue)
END
else IF (((@SrcCodeValueRef) <> '') and ((@SrcCodeValueRef) is not null) )
BEGIN
insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@DecNumber, @DecNumber)
SET @DecNumber = @DecNumber + 1
END
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr
END
CLOSE Mysql
DEALLOCATE Mysql
END
This is my stored procedure..here i have a parameter of variable @MaxLength where i pass the value like 2 or 3 or 4 and depends upon the need.
if i pass 2, the @DecNumber would start from 10 and gets incremented(SET @DecNumber = @DecNumber + 1) up to the total row count
so obviously if the row count exceeds 99, it would go to 3 digit values like 100,101,102 and so on...
my problem is if the @maxLength is 2, at any cost it should maintain distinct two digit values in a sequence...like 0 to 99 and instead of 100 it could be A1 ,A2,A3.....A9, B1....B9,C1..C9 and so on...
i am not asking that i really want this kinda sequence..it may be any kinda sequence only with 2 digits...
I know this question may look odd..but this is my situation..so please help me friends...
Thanks,
Charmer

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

The first problem is that @DecNumber is a BIGINT. You cannot store characters in BIGINT. First you need to change this to CHAR(2) or something that applies. Does the sequence matter? Can it start at AZ and then go to YD? Or are you eventually going to order this?

Jared
CE - Microsoft

Charmer SSChampion Points: 14903 More actions · Answer 2

SQLKnowItAll (3/15/2012)
The first problem is that @DecNumber is a BIGINT. You cannot store characters in BIGINT. First you need to change this to CHAR(2) or something that applies. Does the sequence matter? Can it start at AZ and then go to YD? Or are you eventually going to order this?

ok i will change the data type...sequence is not important....maintaining with unique 2 digit values or 3 digit depends upon the need...it must fit for some large row counts(maximum 2000)...

Thanks,
Charmer

Jared SSC Guru Points: 61793 More actions · Answer 3

Can you create a tally table with these values (if maximum is 2000, I would create 3000 just in case)?

Jared
CE - Microsoft

Charmer SSChampion Points: 14903 More actions · Answer 4

SQLKnowItAll (3/15/2012)
Can you create a tally table with these values (if maximum is 2000, I would create 3000 just in case)?

sorry friend,What is a tally table? i don't have much knowledge with SQL... if you give me an example , i would try to create that one...

Thanks,
Charmer

Jared SSC Guru Points: 61793 More actions · Answer 5

Something similar to this:

CREATE TABLE decNumber (decNumberCount tinyint, decNumber VARCHAR(3))

INSERT INTO decNumber

SELECT 2, '99'

UNION ALL

SELECT 2, 'A0'

UNION ALL

SELECT 2, 'A1'

UNION ALL

SELECT 3, '001'

UNION ALL

SELECT 3, '999'

UNION ALL

SELECT 3, 'A00'

UNION ALL

SELECT 3, 'A01'

In this case, decNumberCount = number of characters and decNumber will be what you will assign. Obviously this needs more rows inserted, but you should get the general idea.

Jared
CE - Microsoft

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 6

Careful with using tinyint here in this case...it doesn't go up to 3000! ::-D

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jared SSC Guru Points: 61793 More actions · Answer 7

MyDoggieJessie (3/15/2012)
Careful with using tinyint here in this case...it doesn't go up to 3000! ::-D

🙂 but this is indicating hw long the decNumber is. The requirement says that it can be 2 or 3 characters.

Jared
CE - Microsoft

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 8

Righto....busted again not reading things carefully enough :ermm:

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jared SSC Guru Points: 61793 More actions · Answer 9

MyDoggieJessie (3/15/2012)
Righto....busted again not reading things carefully enough :ermm:

Seems to be common today as I have had to edit quite a few of my posts :hehe:

Jared
CE - Microsoft

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

The easiest and most elegant way to achieve what you ask is to implement 36-based encoding. I have no time today to show how it can be done in T-SQL, may be tomorrow, or just google implementing n-based encoding you might find something on it.

But it will not work exactly as you want it will not go like:

18

19

20

21

...

99

0A

But like that:

18

19

1A

1B

...

1Z

20

21

...

99

9A

9B

...

ZZ

100

101

...

And so on...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 11

Charmer (3/15/2012)
SQLKnowItAll (3/15/2012)
Can you create a tally table with these values (if maximum is 2000, I would create 3000 just in case)?
sorry friend,What is a tally table? i don't have much knowledge with SQL... if you give me an example , i would try to create that one...

See the following article. If you study it until you understand it, it will change your life insofar as T-SQL goes especially if you can see the possibilities beyond what is in the article.

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 12

Charmer (3/15/2012)
here i have a parameter of variable @MaxLength where i pass the value like 2 or 3 or 4 and depends upon the need.

You give an example of "like 2 or 3 or 4"... Can that number be larger depending "upon the need"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Charmer SSChampion Points: 14903 More actions · Answer 13

Jeff Moden (3/15/2012)
Charmer (3/15/2012)
here i have a parameter of variable @MaxLength where i pass the value like 2 or 3 or 4 and depends upon the need.
You give an example of "like 2 or 3 or 4"... Can that number be larger depending "upon the need"?

yes ..it may be anything between 1 to 9

if it is 1 then it must be unique 1 digit or 1 char value to the total number of rows...

if 2 = 2 digit or 2 char values, 3 = 3 digit or 3 char values and so on....

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 14

Jeff Moden (3/15/2012)
Charmer (3/15/2012)
SQLKnowItAll (3/15/2012)
Can you create a tally table with these values (if maximum is 2000, I would create 3000 just in case)?
sorry friend,What is a tally table? i don't have much knowledge with SQL... if you give me an example , i would try to create that one...
See the following article. If you study it until you understand it, it will change your life insofar as T-SQL goes especially if you can see the possibilities beyond what is in the article.
http://www.sqlservercentral.com/articles/T-SQL/62867/

i don't want in a sequence order...i just want unique values...maintaining unique 1 digit or 1 char value if length is specified as 1 to the total count...and 2 digit or 2 char values if length is specified as 2 and so on...

Thanks,
Charmer