CURSOR returning rows that meet query condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CURSOR returning rows that meet query condition

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-12 : 11:04:42

I have a cursor and it returns each row even if condition is not met

for example I have 65 rows and only 3 rows meet the query condition it still returns 62 blank rows and 3 with data.. I want to only return the 3..
ALSO I would like to have a ROWCOUNT of the rows containing the data

@@param1 nvarchar(max)
as
SET NOCOUNT ON
--DECLARE @colA nvarchar(10)
--DECLARE @colB nvarchar(10)
Declare @@param2 nvarchar(max)
declare @@uid int
Declare @@counter int
Declare @rowcount int
set @rowcount = 0
Set @@counter = 0
SET @@param1 =(SELECT [QDINC\user].[BOA_CleanString](@@param1))
DECLARE MyCursor CURSOR FAST_FORWARD
FOR
SELECT [QDINC\user].[BOA_CleanString]([locName] + '' + [City] + '' + [State]), [uID] FROM dbo.tblLanxLocs
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @@param2, @@uid
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor
INTO @@param2, @@uid
SELECT * FROM (SELECT [QDINC\user].[BOA_Compare](@@param1, @@param2) as hits, @@param2 as DBstring, @@uid as ID)as t WHERE hits > 30
set @@counter=@@counter+1
If @@ROWCOUNT = 1
SET @rowcount = @rowcount + 1
END
CLOSE MyCursor
DEALLOCATE MyCursor
return @rowcount

asgast
Posting Yak Master

149 Posts

Posted - 2009-06-12 : 12:15:14

you are doing something really wrong here

why are you using cursor?

what are you trying to accomplish?

what are hits ?

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-12 : 12:48:50

quote:
Originally posted by asgast

you are doing something really wrong here

why are you using cursor?

what are you trying to accomplish?

what are hits ?

Ok the cursor pulls a record from address table passes it through function1 and uses function2 to compare it to a passed in value (that is also passed through function1)

it works just returns empty rows

hits are the matches of different points on the string as defined in the fuzzy algorythm in function2.. its a 0-100 of how close the strings match higher the hits number the closer the match..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 02:19:31

i think what you need is just this


@param1 nvarchar(max)
as 
SELECT hits,DBstring, ID
FROM
(
SELECT [QDINC\user].[BOA_Compare](@param1, val1) as hits, val1 as DBstring, ID
FROM
(
SELECT [QDINC\user].[BOA_CleanString]([locName] + '' + [City] + '' + [State]) AS val1, [uID] AS ID FROM dbo.tblLanxLocs
)t
)r
 WHERE hits > 30

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-15 : 15:42:54

quote:

Originally posted by visakh16

i think what you need is just this


@param1 nvarchar(max)
as 
SELECT hits,DBstring, ID
FROM
(
SELECT [QDINC\user].[BOA_Compare](@param1, val1) as hits, val1 as DBstring, ID
FROM
(
SELECT [QDINC\user].[BOA_CleanString]([locName] + '' + [City] + '' + [State]) AS val1, [uID] AS ID FROM dbo.tblLanxLocs
)t
)r
 WHERE hits > 30

great thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources