March 23, 2012 at 11:19 am
March 26, 2012 at 10:17 pm
Charmer (3/23/2012)
Lynn Pettis (3/16/2012)
Hi Lynn,
this is a part of my stored procedure, i use cursor here...you all say cursor is not good to be used.
Then what is the best solution instead of cursor? could you tell me based on my stored procedure...so that i will change mine...
declare @Update Varchar(5000)
set @Update = 'UPDATE SrcCodeMap SET PnxCodeValue = SrcCodeValue FROM SrcCodeMap WHERE SrcCodeValue IN' +
'(SELECT DISTINCT CodeValue FROM ' + @DBName + '.DBO.Coded WHERE CodeID =' + Cast(@CodeID as Varchar(15)) +') and CodeId =' + Cast(@CodeID as Varchar(15))
print @Update
exec (@Update)
DECLARE MySql CURSOR FOR SELECT distinct * FROM SrcCodeMap where PnxCodeValue is null and Codeid = + Cast(@CodeID as Varchar(15))
DECLARE @MatchFound smallint
DECLARE @chars varchar(36)
DECLARE @CodeLength smallint
DECLARE @result varchar(25)
DECLARE @RecCnt smallint -- for testing
SET @chars = N'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
set @CodeLength = @MaxLength
set @MatchFound = 1
OPEN Mysql
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@PnxCodeValue,@SysCodeValue,@NewCodeValue, @Vendor,@Misc1 , @Misc2
While @@FETCH_STATUS = 0
If @MatchFound = 1
BEGIN
IF @CodeLength = 1
BEGIN
SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 2
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 3
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 4
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 5
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 6
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
Set @RecCnt = (select COUNT(*) from SrcCodeMap where PnxCodeValue = @result and CodeID = @CodeID)
IF (select COUNT(*) from
(
select PnxCodeValue from SrcCodeMap where Codeid = @CodeID
union
Select distinct CodeValue from tempCodedDB where CodeID = @CodeID
) t
Where PnxCodeValue = @result) = 0
-- ################################### Here Prakash
--IF @RecCnt = 0
Begin
-- SET @MatchFound = 0
update SrcCodeMap set PnxCodeValue = @result,SrcCodeDescr = SrcCodeValue where PnxCodeValue is null and SrcCodeValue = @SrcCodeValue
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@PnxCodeValue,@SysCodeValue,@NewCodeValue, @Vendor,@Misc1 , @Misc2
END
ELSE SET @MatchFound = 1
END
CLOSE Mysql
DEALLOCATE Mysql
END
So, are you planning on responding to my posts or not?
March 27, 2012 at 3:00 am
Lynn Pettis (3/23/2012)
Charmer (3/23/2012)[hr
Found a problem with your code already, as far as trying to work with it. You use a variable @MaxLength, but it is not defined anywhere in the code you provided. Where does this value come from?
Also, you really should specify all your column names instead of using the SELECT *.
Lynn, actually this is my entire stored procedure..
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), @DBName varchar(5000)) AS
BEGIN
DECLARE @MySql varchar(5000), @Insert varchar(5000)
set @Insert = 'Select distinct Codeid,codevalue from ' + @DBName + '.dbo.Coded where CodeId = ' + Cast(@CodeID as Varchar(15))
print @Insert
IF ( @Flag = '2' )
BEGIN
SET @MySql = 'SELECT Distinct 0 as PnxJurisID , ' + Cast(@CodeID as Varchar(15)) + ' as CodedID, LTRIM(RTRIM(B.' + Cast(@SrcCodeValueColumn as varchar(50)) + ')) as SrcCodeValueRef, LTRIM(RTRIM(B.'+ CAST( @SrcCodeValueColumn as varchar(50)) + ')) as SrcCodeValue, LTRIM(RTRIM(B.'+ CAST( @SrcDescColumn as varchar(50)) +')) as SrcCodeDescr ' +
' From dbo.' + @ParentTableName + ' A left outer join SrcCodeMap on LTRIM(RTRIM(A.' + @ColumnName + ')) = LTRIM(RTRIM(SrcCodeMap.SrcCodeValueRef)) and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(15)) +
' left outer join '+ @SrcTablename +' B on LTRIM(RTRIM(B.'+ @SrcCodeValueColumn +')) = LTRIM(RTRIM(A.' + @ColumnName + ')) WHERE 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,LTRIM(RTRIM(' + @ParentTableName+ '.' + @ColumnName + ')) as SrcCodeValueRef, CASE WHEN SCTABE.TECOD# IS NULL THEN LTRIM(RTRIM(' + @ParentTableName+ '.' + @ColumnName + ')) ELSE LTRIM(RTRIM(SCTABE.TECOD#)) END as SrcCodeValue, LTRIM(RTRIM(SCTABE.TEDESC)) as SrcCodeDescr ' +
' From dbo.' + @ParentTableName + ' left outer join SrcCodeMap on LTRIM(RTRIM(' + @ParentTableName + '.' + @ColumnName + ')) = LTRIM(RTRIM(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))
END
else IF ( @Flag = '3' )
BEGIN
SET @MySql = @sql
END
print @MySql
IF OBJECT_ID('tempCodedDB', 'U') IS NOT NULL
DROP TABLE tempCodedDB;
CREATE TABLE tempCodedDB(CodeID varchar(15),CodeValue varchar(60))
Insert into tempCodedDB(CodeID,CodeValue)
exec (@Insert)
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),@PnxCodeValue varchar(50),@SysCodeValue varchar, @NewCodeValue varchar(50),@Vendor varchar, @Misc1 varchar,@Misc2 varchar
-- SET @DecNumber = @Number
INSERT INTO SrcCodeMap( JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr)
SELECT JurisID , CodedID , SrcCodeValueRef , SrcCodeValue , SrcCodeDescr FROM tempSrcCodeDB WHERE SrcCodeValue IS NOT NULL and SrcCodeValue <> ''
declare @Update Varchar(5000)
set @Update = 'UPDATE SrcCodeMap SET PnxCodeValue = SrcCodeValue FROM SrcCodeMap WHERE SrcCodeValue IN' +
'(SELECT DISTINCT CodeValue FROM ' + @DBName + '.DBO.Coded WHERE CodeID =' + Cast(@CodeID as Varchar(15)) +') and CodeId =' + Cast(@CodeID as Varchar(15))
print @Update
exec (@Update)
DECLARE MySql CURSOR FOR SELECT distinct * FROM SrcCodeMap where PnxCodeValue is null and Codeid = + Cast(@CodeID as Varchar(15))
DECLARE @MatchFound smallint
DECLARE @chars varchar(36)
DECLARE @CodeLength smallint
DECLARE @result varchar(25)
DECLARE @RecCnt smallint -- for testing
SET @chars = N'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
set @CodeLength = @MaxLength
set @MatchFound = 1
OPEN Mysql
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@PnxCodeValue,@SysCodeValue,@NewCodeValue, @Vendor,@Misc1 , @Misc2
While @@FETCH_STATUS = 0
If @MatchFound = 1
BEGIN
IF @CodeLength = 1
BEGIN
SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 2
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 3
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 4
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 5
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
IF @CodeLength = 6
BEGIN
SET @result =
SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
+ SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
Set @RecCnt = (select COUNT(*) from SrcCodeMap where PnxCodeValue = @result and CodeID = @CodeID)
IF (select COUNT(*) from
(
select PnxCodeValue from SrcCodeMap where Codeid = @CodeID
union
Select distinct CodeValue from tempCodedDB where CodeID = @CodeID
) t
Where PnxCodeValue = @result) = 0
--IF @RecCnt = 0
Begin
-- SET @MatchFound = 0
update SrcCodeMap set PnxCodeValue = @result,SrcCodeDescr = SrcCodeValue where PnxCodeValue is null and SrcCodeValue = @SrcCodeValue
FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@PnxCodeValue,@SysCodeValue,@NewCodeValue, @Vendor,@Misc1 , @Misc2
END
ELSE SET @MatchFound = 1
END
CLOSE Mysql
DEALLOCATE Mysql
END
This is the way i execute by passing parameters...
exec sp_coded 'PSAJCK', 'AJSEX', 1085, 9, 1, '0025', '1','', '', '', '','DBName'
Thanks,
Charmer
March 27, 2012 at 4:33 am
Lynn Pettis (3/23/2012)
Charmer,Let me guess, this code will generate a random string of characters from 1 to 6 characters in length, correct?
Have I got something for you if that is what it does!
The code in the function just needs a little change to do what your code does.
No loops, no cursors.
First of all Lynn,I regret for not answering for your question three days before since i was outta the office since three days..
ok Lynn, for example, let me explain with my below execute statement
exec sp_coded 'PSAJCK', 'AJSEX', 1085, 9, 1, '0025', '1','', '', '', '','DBName'
PSAJCk (customer's table name),AJSEX(column name in PSAJCK), 1085 (my codeid value in CODED table ) , 9 = don't want for now as i changed the concept, 1 (@MaxLength for random string generation),0025 (customer codeid in SCTABE table), 1 (@Flag condition),DBName(My database name which has CODED table)
PSAJCK will be having values like F,M,U where the description could be found in SCTABE table under TEDESC column...
My CODEID column in CODED table = TECOD# in SCTABE table
first i get the code values from the AJSEX column in PSAJCK table, description from SCTABE table and then i insert all value into my SrcCodeMap table..like codevalue as SRCCodeValueRef,codevalue as SRCCodeValue , SCTABE description as SrccodeDescr
i just want to compare my codevalue column from CODED table with AJSEX column, and if matched then update the codevalue as PnxCodeValue...in SrcCodeMap table, and if not matched then , random string as PnxCodeValue..
This is for what exactly i use stored procedure...
I hope you can understand a little bit of my concept...
If you did not , forgive me and il try to explain with better..
I here attached the DDL and DML
Thanks,
Charmer
March 27, 2012 at 5:35 am
Lynn Pettis (3/23/2012)
No loops, no cursors.
but Lynn,
With out loop how can i avoid duplicate random string....
i took the cursor out of the stored procedure...so if i have 6 rows to be updated, it it updates with same random string...i don't want to be like that....needs to be unique string for each rows...
Thanks,
Charmer
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply