March 15, 2012 at 3:21 pm
er.sivaganesh (3/15/2012)
sorry one question but we can have loop it also possible or not in sql server
It is possible, but not optimal in most cases. In SQL we work with data sets. Why would you want to loop 3 or 4 times when you can do it once?
Jared
CE - Microsoft
March 16, 2012 at 1:49 pm
Thanks,
Jared
SQL Know-It-All for u job s for me and i inform u solved this problem by using split function and i used cursor for looping
and thanks each one of u who joined in this disscussion
March 16, 2012 at 1:54 pm
er.sivaganesh (3/16/2012)
Thanks,Jared
SQL Know-It-All for u job s for me and i inform u solved this problem by using split function and i used cursor for looping
and thanks each one of u who joined in this disscussion
I don't know why you are using a cursor. That will kill your performance if it is not needed. I still think it is better to use the REPLACE function on the parameter and add the % to the beginning and end. This holds true if you are using 1 search box and not multiple. There is no need to loop or split then.
Jared
CE - Microsoft
March 16, 2012 at 2:05 pm
SQLKnowItAll (3/16/2012)
er.sivaganesh (3/16/2012)
Thanks,Jared
SQL Know-It-All for u job s for me and i inform u solved this problem by using split function and i used cursor for looping
and thanks each one of u who joined in this disscussion
I don't know why you are using a cursor. That will kill your performance if it is not needed. I still think it is better to use the REPLACE function on the parameter and add the % to the beginning and end. This holds true if you are using 1 search box and not multiple. There is no need to loop or split then.
Simple, that is what he wants to use and no amount discussion will convince him otherwise.
March 23, 2012 at 9:59 am
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
Thanks,
Charmer
March 23, 2012 at 10:06 am
Charmer (3/23/2012)
Lynn Pettis (3/16/2012)
Now, we will need the DDL for the table(s) (CREATE TABLE statement(s)), sample data (series of INSERT INTO statements) for the table(s), and the expected results based on the sample data.
March 23, 2012 at 10:14 am
Also, please explain what your code is doing. I'm sorry but I'm not a mind reader and it would help to know.
March 23, 2012 at 10:17 am
Lynn Pettis (3/23/2012)
Charmer (3/23/2012)
Lynn Pettis (3/16/2012)
Now, we will need the DDL for the table(s) (CREATE TABLE statement(s)), sample data (series of INSERT INTO statements) for the table(s), and the expected results based on the sample data.
sample data..!! Lynn, the table has million of records...when i generate scripts, i need to limit the rows..how can i do this Lynn?
so that i would post sample insert data over here...
Thanks,
Charmer
March 23, 2012 at 10:27 am
SELECT TOP 10 ...
There is an article about it, link at the bottom of my signature.
March 23, 2012 at 10:30 am
Eugene Elutin (3/23/2012)
SELECT TOP 10 ...There is an article about it, link at the bottom of my signature.
Elutin, i could not find any link..
Thanks,
Charmer
March 23, 2012 at 10:31 am
Charmer (3/23/2012)
Lynn Pettis (3/23/2012)
Charmer (3/23/2012)
Lynn Pettis (3/16/2012)
Now, we will need the DDL for the table(s) (CREATE TABLE statement(s)), sample data (series of INSERT INTO statements) for the table(s), and the expected results based on the sample data.
sample data..!! Lynn, the table has million of records...when i generate scripts, i need to limit the rows..how can i do this Lynn?
so that i would post sample insert data over here...
Sample data, doesn't mean the entire table. Just enough to represent the problem domain.
March 23, 2012 at 10:32 am
Charmer (3/23/2012)
Eugene Elutin (3/23/2012)
SELECT TOP 10 ...There is an article about it, link at the bottom of my signature.
Elutin, i could not find any link..
Look in his signature block. He did say the link was below.
March 23, 2012 at 10:36 am
March 23, 2012 at 10:37 am
Charmer (3/23/2012)
Lynn Pettis (3/23/2012)
Charmer (3/23/2012)
Lynn Pettis (3/16/2012)
Now, we will need the DDL for the table(s) (CREATE TABLE statement(s)), sample data (series of INSERT INTO statements) for the table(s), and the expected results based on the sample data.
sample data..!! Lynn, the table has million of records...when i generate scripts, i need to limit the rows..how can i do this Lynn?
so that i would post sample insert data over here...
Also, still waiting for an explaination as to what the code is doing. There are absolutly no comments in the code to help anyone to undestand what it is doing. I really feel sorry for the person that has to follow you if you were hit by the proverbial bus.
March 23, 2012 at 11:11 am
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 *.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply